[RESOLVED] SQL Nightmare! Please help

[dinosaur_uk]

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

[GaryMazzone]

Try

Select 
   Max(Id) As ID,
   ItemNo ,
   Max(SalesDate) As SalesDate,
   Max(SalesID) As SalesID
From Sales
Group By ItemNO

[Grimfort]

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:

Select ItemNo, Max(SaleDate), SalesID
From SalesTable
Group By ItemNo, SalesID

[techgnome]

@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...

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:

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

[Grimfort]

@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.

[techgnome]

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