[Un-Solved] First instance only

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

[Un-Solved] First instance only

Post 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
Last edited by John Cartwright on Wed Jul 20, 2005 6:26 pm, edited 4 times in total.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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)
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
Post Reply