By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,863 Members | 838 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,863 IT Pros & Developers. It's quick & easy.

Getting a single column to only show one record.

P: n/a
Hello,

I have a table that shows:
Serial Number Date Returned
2333005Y 8/15/2006
2333005Y 7/18/2003
2333005Y 1/1/2004
2333005Y 7/5/2006
2333005Y 11/24/2005
2333005Y 9/8/2005
2333005Y 9/8/2005
2333005Y 11/24/2005
2333014Y 7/18/2007
2333014Y 9/6/2005
2333014Y 7/23/2003
2333014Y 10/1/2001
2333014Y 1/25/2004
2333014Y 8/31/2007
2333014Y 9/6/2005
2333014Y 1/5/2006
2333014Y 1/25/2004
2333022Y 11/19/2006
2333022Y 8/22/2002
2333022Y 6/26/2006
2333022Y 1/1/2004
2333022Y 9/2/2003
2333022Y 1/1/2004
2333022Y 9/2/2003
2333022Y 8/22/2002
2333040Y 8/26/2002
2333040Y 11/20/2002
2333040Y 7/20/2003
2333040Y 4/26/2004
2333040Y 1/1/2004
2333040Y 5/29/2006
2333040Y 11/10/2003
2333040Y 1/1/2004
2333040Y 4/26/2004
2333040Y 8/26/2002

and so on. I want it to make a query that shows only one entry for the
serial number and match it with the most recent date there.So the
resulting query looks like this.

Serial Number Date Returned
2333005Y 8/15/2006
2333014Y 8/31/2007
2333022Y 11/19/2006
2333040Y 5/29/2006

and so on. I cannot figure out how to do this. Distinct is for each
row, and the Max function for the Date didn't seem to work, although I
might be doing it wrong.

Any help you could provide would be greatly appreciated.

Thank you,
Dec 21 '07 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Try

SELECT [Serial Number], Max([Date Returned])
FROM YourTable
GROUP BY [Serial Number]

You may have problems with the date as 9/8/2005 could be 9th August or 8th
September depending on how your system proccesses dates

Phil

"colemanj4" <co*******@gmail.comwrote in message
news:47**********************************@e23g2000 prf.googlegroups.com...
Hello,

I have a table that shows:
Serial Number Date Returned
2333005Y 8/15/2006
2333005Y 7/18/2003
2333005Y 1/1/2004
2333005Y 7/5/2006
2333005Y 11/24/2005
2333005Y 9/8/2005
2333005Y 9/8/2005
2333005Y 11/24/2005
2333014Y 7/18/2007
2333014Y 9/6/2005
2333014Y 7/23/2003
2333014Y 10/1/2001
2333014Y 1/25/2004
2333014Y 8/31/2007
2333014Y 9/6/2005
2333014Y 1/5/2006
2333014Y 1/25/2004
2333022Y 11/19/2006
2333022Y 8/22/2002
2333022Y 6/26/2006
2333022Y 1/1/2004
2333022Y 9/2/2003
2333022Y 1/1/2004
2333022Y 9/2/2003
2333022Y 8/22/2002
2333040Y 8/26/2002
2333040Y 11/20/2002
2333040Y 7/20/2003
2333040Y 4/26/2004
2333040Y 1/1/2004
2333040Y 5/29/2006
2333040Y 11/10/2003
2333040Y 1/1/2004
2333040Y 4/26/2004
2333040Y 8/26/2002

and so on. I want it to make a query that shows only one entry for the
serial number and match it with the most recent date there.So the
resulting query looks like this.

Serial Number Date Returned
2333005Y 8/15/2006
2333014Y 8/31/2007
2333022Y 11/19/2006
2333040Y 5/29/2006

and so on. I cannot figure out how to do this. Distinct is for each
row, and the Max function for the Date didn't seem to work, although I
might be doing it wrong.

Any help you could provide would be greatly appreciated.

Thank you,

Dec 21 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.