Welcome to WebmasterWorld Guest from 54.145.144.101

Forum Moderators: open

Message Too Old, No Replies

MySQL

Select first occurence

   
8:57 pm on Jan 8, 2008 (gmt 0)

10+ Year Member



I have a table that can contains multiple records for a given id.

Example

DATE--------ID------NAME------COLOR

2008-01-01---1------John------Red
2008-01-02---1------John------Green
2008-01-03---1------John------Blue
2008-01-05---2------Paul------Red
2008-01-06---2------Paul------Green
2008-01-07---2------Paul------Blue

Based on the above how would I select the first record entered for each ID so I get the following result?

DATE--------ID------NAME------COLOR

2008-01-01---1------John------Red
2008-01-05---2------Paul------Red

Any help would be really appreciated.

9:06 pm on Jan 8, 2008 (gmt 0)

WebmasterWorld Senior Member henry0 is a WebmasterWorld Top Contributor of All Time 10+ Year Member



SELECT DISTINCT
Should do it
if not post your query
9:21 pm on Jan 8, 2008 (gmt 0)

10+ Year Member



Hi Henry0

select distinct date,id,name,color from table

This returns all records

9:37 pm on Jan 8, 2008 (gmt 0)

10+ Year Member



I think the following will work, but may not be the most efficient way of doing it:

select date,id,name,color
from table
where concat(date,id) in
(select concat(min(date),id))
from table
group by id)

3:44 am on Jan 9, 2008 (gmt 0)

5+ Year Member



I think an order by ID group by ID would work to do what you want. Try it out and see (you may have to flip order by id and group by id to get it to work. If that doesn't work try the above options with DESC after the ID that goes with order by.
 

Featured Threads

Hot Threads This Week

Hot Threads This Month