Where clause that gets first of each group 1

[Phailak]

I have a table that has a scandate, name and other customer data. The problem is the application duplicates the customer information with each new scan like:

2007-05-25 George M 45 50$
2006-08-08 Allie F 50 70$
2005-07-07 Allie F 50 80$
2004-05-05 Allie F 50 90$

I want to extract one record per distinct name and use ALL the information of the first record according to date to have a result as so

2007-05-25 George M 45 50$
2004-05-05 Allie F 50 90$

I'm assuming it has to do with the WHERE statement or perhaps the HAVING statement? Any suggestions?

 

[gmmastros]

In my opinion, the easiest way to do this is with a subquery, like this...

Select Table.*
From   Table
       Inner Join (
         Select NameColumn, Min(DateColumn) As DateColumn
         From   Table
         Group By NameColumn
         ) As A
         ON  A.NameColumn = Table.NameColumn
         And A.DateColumn = Table.DateColumn

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sunila7]

Try this:

select * from TBLNAME t
INNER JOIN (SELECT min(dateFLD) dt, NAMEFLD from TBLNAME group by NAMEFLD) 
t1 ON t.dateFLD = t1.dateFLD and t.NAMEFLD = t1.NAMEFLD

Sunil

 

[Phailak]

Thanks, I'll give it a try tonight!

 

[Phailak]

Works like a charm, thanks