Help with query picking highest value

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

SQL Server 2005 Forums

Transact-SQL (2005)

Help with query picking highest value

Author

Topic

BrianBurgit
Starting Member

12 Posts

Posted - 2008-07-14 : 15:41:57

I have data like this

RefNum ProdID ModDate Version
1 101 1/1/08 1
2 102 1/1/08 1
3 102 3/1/08 2
4 103 1/1/08 1
5 104 1/1/08 1
6 104 2/2/08 2
7 104 3/3/08 3
8 104 4/4/08 4
9 105 1/1/08 1
10 105 6/12/08 2

How do I Select the RefNum of the most recent version of each product?

Thanks,

Brian

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2008-07-14 : 16:02:08

SELECT a.RefNum
FROM
yourtable a
INNER JOIN
(select prodnum,MAX(version) as Version from yourtable) b
ON
a.prodnum = b.prodnum
and a.version = b.version

Jim

BrianBurgit
Starting Member

12 Posts

Posted - 2008-07-14 : 16:08:05

Thanks Jim, perfect

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-15 : 03:37:23

or

select * from table t where version=(select max(version) from table where prodnum=t.prodnum)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-07-15 : 03:45:03

SQL Server 2005 style

DECLARE	@Sample TABLE (RefNum INT, ProdID INT, ModDate DATETIME, Version INT)

INSERT	@Sample
SELECT	 1, 101, '1/1/08',  1 UNION ALL
SELECT	 2, 102, '1/1/08',  1 UNION ALL
SELECT	 3, 102, '3/1/08',  2 UNION ALL
SELECT	 4, 103, '1/1/08',  1 UNION ALL
SELECT	 5, 104, '1/1/08',  1 UNION ALL
SELECT	 6, 104, '2/2/08',  2 UNION ALL
SELECT	 7, 104, '3/3/08',  3 UNION ALL
SELECT	 8, 104, '4/4/08',  4 UNION ALL
SELECT	 9, 105, '1/1/08',  1 UNION ALL
SELECT	10, 105, '6/12/08', 2

SELECT	RefNum,
	ProdID,
	ModDate,
	Version
FROM	(
		SELECT	RefNum,
			ProdID,
			ModDate,
			Version,
			ROW_NUMBER() OVER (PARTITION BY ProdID ORDER BY Version DESC) AS RecID
		FROM	@Sample
	) AS d
WHERE	RecID = 1

E 12°55'05.25"
N 56°04'39.16"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-15 : 08:10:42

[code]SELECT DISTINCT t.ProdID,b.*
FROM @Sample t
CROSS APPLY (SELECT TOP 1 *
FROM @Sample
WHERE ProdID=t.ProdID
ORDER BY Version DESC)b[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources