homepage Welcome to WebmasterWorld Guest from 54.144.197.140
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
MySQL
Select first occurence
gosman

10+ Year Member



 
Msg#: 3543514 posted 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

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



 
Msg#: 3543514 posted 9:06 pm on Jan 8, 2008 (gmt 0)

SELECT DISTINCT
Should do it
if not post your query

gosman

10+ Year Member



 
Msg#: 3543514 posted 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

10+ Year Member



 
Msg#: 3543514 posted 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

5+ Year Member



 
Msg#: 3543514 posted 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.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved