473,320 Members | 1,936 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Getting a single column to only show one record.

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
1 1592
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Dave Sisk | last post by:
Hey folks: I'm trying to do this: CREATE TRIGGER datawhse.emp_ti AFTER INSERT ON emp REFERENCING NEW AS n FOR EACH ROW MODE DB2ROW BEGIN DECLARE v_rrn DECIMAL(15,0);
3
by: daveS | last post by:
I have come accross the exact same problem as below. The address that was given as a solution cannot be found. Specifically, i need to be able to show labels in only the first column of a report!...
24
by: Mike L | last post by:
This is for a Win form, in C# 2005. I want to load a datagrid, make some columns width 0, and then clean out the record I added. I get the error message, ""Index was out of range. Must be...
7
by: gemel | last post by:
I am developing an application that uses SQL 2000 as my source of images. I have successfully created the code to load the images onto the SQL Server and also to retrieve the images into a dataset....
11
by: Siv | last post by:
Hi, I seem to be having a problem with a DataAdapter against an Access database. My app deletes 3 records runs a da.update(dt) where dt is a data.Datatable. I then proceed to update a list to...
7
by: PW | last post by:
Hi, I have a form with unbound fields on it. The user selects a record from a recordset and I populate the unbound fields. When I try to change the unbound quantity text box, Access 2003 tells...
4
by: cbtechlists | last post by:
I have an ASP app that we've moved from a Windows 2000 to a Windows 2003 server (sql server 2000 to sql server 2005). The job runs fine on the old servers. Part of the app takes a recordset and...
4
by: bushi | last post by:
hi ! i have following code to display some text on a web form,after getting it from database. <asp:DataList ID="DataList1" runat="server" DataSourceID="SqlDataSource1"> <ItemTemplate>...
7
by: =?Utf-8?B?TG9zdEluTUQ=?= | last post by:
Hi All :) I'm converting VB6 using True DBGrid Pro 8.0 to VB2005 using DataGridView. True DBGrid has a MultipleLines property that controls whether individual records span multiple lines. Is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.