Grabing Earliest Record 1

[DBAssam]

I have a table with 1,000's of archive records that has the following fields:

uniquekey, reference, datewritten, amount

I need to run a report that, based on the 'reference' field extracts the earliest record that was inserted for a given 'reference'. I would appreciate any help. Many thanks.

 

[mwolf00]

SELECT TOP 1 * FROM myTable WHERE refField = 'this reference' ORDER BY dateWritten

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook

Tsuna​

 

[DBAssam]

Thanks Mwolf00 - but, I need a complete listing of all records from the table showing just the earliest records:

reference datewritten
ABC 01/01/05
ABC 01/02/05
ABC 01/02/05
XYZ 01/01/05
XYZ 01/02/05

Report would need to only select:

ABC 01/01/05
XYZ 01/01/05

Sorry. Cheers

 

[Lothario]

Try this:

select reference,datewritten
from yourtable t1
WHERE t1.datewritten In ( Select top 1 t2.datewritten
from yourtable t2 Where t1.reference=t2.reference
Order by t2.datewritten DESC )

-L

 

[SQLSister]

Select reference, min(datewritten) from yourtable
Group By reference

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[maswien]

If your table only have two columns 'reference' and 'datewritten', following would be the SQL:

select reference, min(datewritten)
from myTable
group by reference