SQL Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

SQL Question

Author

Topic

benji012e
Starting Member

12 Posts

Posted - 2007-06-07 : 20:55:49

Hi,

I've got a table of orders, with fields like:

Order Number <- UID
Customer number
Product ID Number
Order Date

Is there a way to query such that it will return to me the most recent order entry for every single product id number? I'm thinking it might have to do with SELECT TOP 1, but I need it to spread out by Product ID Number and sort by date (to get only the 1 most recent). Is this possible? A fallback is to have a loop that will query once for every single product ID and do "TOP 1" each time, but that seems very inefficient. Any thoughts?

Thanks!

-Ben

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-06-07 : 21:09:01

Post some sample data from the table and the expected output and someone can help with the query.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

benji012e
Starting Member

12 Posts

Posted - 2007-06-07 : 22:15:34

Thanks for the reply:

ID Product Code Date Purchased Purchased By
4 1 5/31/2007 John
5 1 6/2/2007 Jane
6 2 5/31/2007 Kate
7 2 6/1/2007 Kat
8 3 5/31/2007 Tom
9 3 6/1/2007 Tim

I'd like the returned result to be:

ID Product Code Date Purchased Purchased By
5 1 6/2/2007 Jane
7 2 6/1/2007 Kat
9 3 6/1/2007 Tim

I can figure out a query to obtain the "TOP 1" of just 1 prouduct code (and write three separate queries, potentially doing the query inside a loop and manually concatinating the results of all the queries):

SELECT TOP 1 Table1.[Product Code], Table1.[Date Purchased], Table1.[Purchased By]
FROM Table1
WHERE (((Table1.[Product Code])=1))
ORDER BY Table1.[Date Purchased] DESC;

SELECT TOP 1 Table1.[Product Code], Table1.[Date Purchased], Table1.[Purchased By]
FROM Table1
WHERE (((Table1.[Product Code])=2))
ORDER BY Table1.[Date Purchased] DESC;

SELECT TOP 1 Table1.[Product Code], Table1.[Date Purchased], Table1.[Purchased By]
FROM Table1
WHERE (((Table1.[Product Code])=3))
ORDER BY Table1.[Date Purchased] DESC;

But I'm wondering if there's a single query that can do this?

Thanks for any help...

-Ben

benji012e
Starting Member

12 Posts

Posted - 2007-06-25 : 12:07:15

Does anyone have any suggestions regarding this?

-Ben

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-25 : 13:05:31

In Access, create a query that returns the OrderNumber and Max(OrderDate) from your table.
Then create another query that links the results of this query to your original table on OrderNumber and OrderDate to return all the detail information for each latest order record.

e4 d5 xd5 Nf6

benji012e
Starting Member

12 Posts

Posted - 2007-06-25 : 15:03:41

Thanks for the reply, blindman. Do you know of any material detailing how to link one query to the results of another? Are you talking about an embedded select statement of sorts?

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-25 : 15:33:14

In the MS Access GUI query designer, you have the option of including both tables and stored queries. Its right there in the dialog box.

e4 d5 xd5 Nf6

benji012e
Starting Member

12 Posts

Posted - 2007-06-27 : 10:24:31

For the first query, if I return ordernumber and max(orderdate), it has no ability to really do a "max" calculation because it must group by oldernumber, which is a unique ID so the max function seems not to be able to compare any results. What looks more like what I want is to have ProductCode and Max(orderdate). This does return fewer results than the entire table: it returns the date of the most recent order for each product code. Am I headed in the wrong direction?

Also, when I try to create the second query, do I need to specify a relationship between the query and the table? I would think that it would be through the OrderID, but if I add OrderID to the query, I get the same problem I got originally where nothing could be done really with Max because of the UniqueID preventing summing.

Any thoughts?

Thanks...

-Ben

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-27 : 11:19:50

Why are you grouping by OrderID? I thought you wanted the latest record for each product code?

e4 d5 xd5 Nf6

benji012e
Starting Member

12 Posts

Posted - 2007-06-27 : 16:08:49

That's correct, but in your earlier post to me, you said:

"In Access, create a query that returns the OrderNumber and Max(OrderDate) from your table."

OrderNumber is the same as OrderID, I assume?

benji012e
Starting Member

12 Posts

Posted - 2007-06-29 : 16:09:36

Does anyone have any additional thoughts here? I'd love to figure this out.

Thanks...

-Ben

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2007-06-29 : 16:29:41

Ben....
Use a subquery to get the latest record for any attribute you want. Then link that subquery back to the primary table on both the attribute and the datetime value to get the details for that record.
This should not be that complicated...

e4 d5 xd5 Nf6

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources