Get just the last record based on a history sequence #

[sjjustina]

This is my query:
SELECT product, loc, protype, ohvolumequantity, histseq
FROM dbo.pro_itm_hst
ORDER BY loc, protype, product, histseq

Every time the inventory is changed a history record is writting into this table to reflect the current on hand quantity. The first three fields are the same but the ohvolumequantity changes and the histseq is incremented.

I need to pull only one record for each unique Loc, Product, type, the one with the highest histseq #. I started to use MAX but that doesn't work.

Help! Thanks, Sarah

 

[SQLSister]

did you use group by when you used max?

show us what you did and what the data is like and what results you want, please.

Questions about posting. See faq183-874

 

[sjjustina]

SELECT TOP 100 PERCENT product, loc, protype, ohvolumequantity, MAX(histseq) AS Expr1
FROM dbo.pro_itm_hst
GROUP BY loc, protype, product, ohvolumequantity, histseq
ORDER BY loc, protype, product, histseq

The first product, I only want the second row:
Using group by and MAX this is some data:
IP41D B LV 8.052 1
IP41D B LV 0 2
IP4X41 B LV 1.345 1
IP4X51 B LV 2.117 1
IP4X61 B LV 11.129 1
IP51D B LV 8.542 1
IP51D B LV 0 2
JA1 B LV 9.5 1
JA10SB B LV 2.119 1
JA10SB B LV 0 2

 

[monksnake]

This may not be the most efficient way, but it'll return the entire row of each

Select   a.product, a.loc, a.protype, a.quantity, b.histseq
From     @table1 a 
   Inner Join (Select product, max(histseq) as histseq from @table1 Group By product) b 
   On a.product = b.product and a.histseq = b.histseq
Order By a.loc, a.protype, a.product

<.