MySQL

Forum Moderators: open

Message Too Old, No Replies

MySQL

Select first occurence

gosman

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

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.

henry0

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

SELECT DISTINCT
Should do it
if not post your query

gosman

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

Hi Henry0

select distinct date,id,name,color from table

This returns all records

mark_roach

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

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)

8kobe

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

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.

MySQL

Select first occurence

gosman

henry0

gosman

mark_roach

8kobe

Join The Conversation

Moderators and Top Contributors

Hot Threads This Week