help with a query - SQL Server Forums

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 2008 Forums

Transact-SQL (2008)

help with a query

Author

Topic

aviv.sages
Starting Member

2 Posts

Posted - 2013-07-13 : 16:15:28

hi,

I have a table:

version item price
1 a 1.00
2 a 1.20
3 a 1.30
1 b 2.00
2 b 2.20

I need to create a query that will select the latest version price for each item
the outcome should be like that:
version item price
3 a 1.30
2 b 2.20

please help

regards,
aviv

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-13 : 17:48:12

use one of these

SELECT
	b.version,	
	a.item,
	b.price
FROM
	YourTable a
	CROSS APPLY
	(SELECT TOP (1) version, price
	 FROM YourTable b 
	 WHERE b.item = a.item
	 ORDER BY version DESC
	)b;
	
;WITH cte AS
(
	SELECT *,
		ROW_NUMBER() OVER (PARTITION BY item ORDER BY version DESC) N
	FROM YourTable
) SELECT version, item, price FROM cte 
  WHERE N = 1;

aviv.sages
Starting Member

2 Posts

Posted - 2013-07-17 : 16:13:33

Hi James,

Thank you very much.

Aviv

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources