Return highest inventory value per product

Question

Post reply

Return highest inventory value per product

2Tall

SSChampion

Points: 14644
More actions
November 16, 2010 at 10:14 am

#388241

Hi. I have a query that returns inventory records. At present 112000 records are returned. Each product may be returned 100's of times. I am only interested in the last movement for each product.
Breaking all the rules I know but here goes:
Inventory..........Product
12345...............ABC
12346...............ABC
12347...............ABC
12348...............ABC
12349...............ZZZ
12350...............ZZZ
12351...............ZZZ
12352...............ZZZ
Query to return
Inventory..........Product
12348...............ABC
12352...............ZZZ
I hope that all makes sense.
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

A simple GROUP BY will do the trick....

DECLARE @Table TABLE (Inventory int, Product varchar(10))

INSERT INTO @Table

SELECT 12345, 'ABC' UNION ALL

SELECT 12346, 'ABC' UNION ALL

SELECT 12347, 'ABC' UNION ALL

SELECT 12348, 'ABC' UNION ALL

SELECT 12349, 'ZZZ' UNION ALL

SELECT 12350, 'ZZZ' UNION ALL

SELECT 12351, 'ZZZ' UNION ALL

SELECT 12352, 'ZZZ'

SELECT MAX(Inventory),

Product

FROM@Table

GROUP BY Product

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

2Tall SSChampion Points: 14644 More actions · Answer 2

Thanks John.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper