-
Feb 14th, 2011, 11:43 AM
#1
Thread Starter
Frenzied Member
[RESOLVED] SQL Nightmare! Please help
Hi there,
I have a tricky SQL problem which I have been trying to solve.
Sales Table
ID ItemNo SaleDate SalesID
1 123 01/01/2011 100256
2 100 03/01/2011 265568
3 102 01/01/2011 654882
4 123 04/01/2011 235854
5 100 10/01/2011 323158
I want the SQL to only return only the latest SalesID for each item so I would like the result to be:
Results
ID ItemNo SaleDate SalesID
3 102 01/01/2011 654882
4 123 04/01/2011 235854
5 100 10/01/2011 323158
If you find my thread helpful, please remember to rate me
-
Feb 14th, 2011, 12:31 PM
#2
Re: SQL Nightmare! Please help
Try
sql Code:
Select
Max(Id) As ID,
ItemNo ,
Max(SalesDate) As SalesDate,
Max(SalesID) As SalesID
From Sales
Group By ItemNO
Sometimes the Programmer
Sometimes the DBA
Mazz1
-
Feb 14th, 2011, 12:32 PM
#3
Re: SQL Nightmare! Please help
Its not quite a nightmare, I guess that you have not had to create many SQL statements before.
One possible way is to select the Max of a column, so in your case the SaleDate, and GroupBy on the Item. So something like:
Code:
Select ItemNo, Max(SaleDate), SalesID
From SalesTable
Group By ItemNo, SalesID
Last edited by Grimfort; Feb 14th, 2011 at 12:39 PM.
-
Feb 14th, 2011, 12:42 PM
#4
Re: [RESOLVED] SQL Nightmare! Please help
@Gary... but that gets the max date and the max salesID, but what if the max date is linked with the min salesID
@Grim - unless the field is part of an aggregate, it must be included in the grouping... plus you grouped by something that wasn't in the select.
@dino - Grim is right though, it's not a nightmare, and is a common problem... you just have to think about it in two steps: First, get the list with the max date...
Code:
SELECT ItemNo, MAX(SalesDate) MDate FROM SalesTable Group By ItmeNo
This will give you the most recent date for each ItemNo.
Now comes step two, getting the rest of the info. So now, at this point you want the data from the original table where the data matches our current selection. A simple inner join takes care of this:
Code:
SELECT ST.*
FROM SalesTable ST
INNER JOIN (SELECT ItemNo, MAX(SalesDate) MDate
FROM SalesTable Group By ItmeNo) D
ON ST.ItemNo = D.ItemNo AND ST.SalesDate = D.MDate
And that's all she wrote. The end result should be every row in the SalesTable where we have a matching record on the ItemNo and SalesDate, which has been set as the max for each ItemNo.
-tg
-
Feb 14th, 2011, 12:57 PM
#5
Re: [RESOLVED] SQL Nightmare! Please help
Originally Posted by techgnome
@Grim - unless the field is part of an aggregate, it must be included in the grouping... plus you grouped by something that wasn't in the select.
Typo I tell thee, I edited it before you posted . (well, maybe heh) I do like the InnerJoin.
PS: Fix yours in the 2nd code part.
-
Feb 14th, 2011, 01:07 PM
#6
Re: [RESOLVED] SQL Nightmare! Please help
I leave typos in mine.... that way it can't be simply copied and pasted. Yeah, yeah... that's my story I'm sticking to it...
The technique I showed there is a common one... and one that I've used a lot... A LOT! over the years. Two jobs ago, the system I worked with was a pricing system, based on market... so the price could change daily (and in some extreme cases we tracked down to the hour!) so it was important to make sure we continuously had the most recent rate for the given time. So that's actually a simplified version of what I've had to deal with..
-tg
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|