Page 1 of 1

[Un-Solved] First instance only

Posted: Wed Jul 20, 2005 10:58 am
by John Cartwright
Kind of a stupid question but I've never encountered this.

Lets say my table had a column called "troutd", how could I grab only the first instance of that troutd only using mysql 4.1

For example the column would look like

TROUTD
1
2
3
4
5
4
1
3
4
5

I only want to grab the first instance of each number, but the problem is I will be joining multiple tables together and don't need to just get the distinct troutd. Not sure how to go about getting multiple columns where 1 column is only distinct. Help appreciated. Thanks

Edit | added to post

Posted: Wed Jul 20, 2005 12:28 pm
by timvw

Code: Select all

SELECT DISTINCT troutd
FROM table
(pay attention to distinct, because it applies to the whole row and not the column right after it)

Posted: Wed Jul 20, 2005 1:46 pm
by John Cartwright
Ended up using GROUP as Tim suggested. Final result for those interested:

Code: Select all

$result = $database->query('SELECT * 
									FROM REQUEST AS R1
									INNER JOIN RESPONSE USING ( INTRN_SEQ_NUM ) 
									WHERE TROUTD NOT IN ( 
														 SELECT ORIG_TROUTD
														 FROM REFUNDS AS R2
														 WHERE R1.TROUTD <> R2.ORIG_TROUTD OR R1.TROUTD <> R2.NEW_TROUTD
														)
									AND UNIX_TIMESTAMP(R1.TRANS_DATE) >= \''.strtotime($date.'01') .'\' AND UNIX_TIMESTAMP(R1.TRANS_DATE) < \''.strtotime($date.date('t',strtotime($date.'01'))).'\'
									GROUP BY R1.TROUTD ');
arggggggggggggggggggg