We had a few spreadsheets get imported multiple times into a database so we now have duplicate data (which of course had primary keys etc been assigned this would not have happed...)

However - the issue at hand is I need a way to return the data associated with the smallest (oldest) date for each PN in the database. Below is a sample of the data and the structure. I tried to use Top 1 but I realized there was no rhyme or reason to what SSMS deemed as the 1st entry.

What would a query be for the syntax below to return the oldest date - expected returned results:

Number One 111111 2014-01-01
Number Two 2222 2014-02-22

Sample DDL ->

  Declare @Test Table (pn varchar(200), ECN int, d1 date)
  Insert Into @Test (pn, ECN, d1) Values
  ('Number One', '111111', '2014-01-01')
  ,('Number One', '111111', '2014-01-31')
  ,('Number One', '111111', '2014-02-14')
  ,('Number Two', '2222', '2014-02-22')
  ,('Number Two', '2222', '2014-02-25')
  • [HS] please, don't delete your question when people make answer, that's rude. BTW, here is something that should make you understand group by sqlfiddle.com/#!9/cca27a/1
    – Blag
    May 10 '17 at 22:14
  • @Blag - Sincerest apology. Thank you for that sqlfiddle that does help clear things up. May 11 '17 at 2:01
SELECT pn, MIN(d1) AS [oldestDate]
FROM @Test

pn          oldestDate
Number One  2014-01-01
Number Two  2014-02-22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.