homepage Welcome to WebmasterWorld Guest from 54.83.133.189
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Pubcon Website
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
MySQL
Select first occurence
gosman




msg:3543516
 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




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

SELECT DISTINCT
Should do it
if not post your query

gosman




msg:3543529
 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




msg:3543537
 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




msg:3543717
 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.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved