Choose rows depended on two rows

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

Transact-SQL (2012)

Choose rows depended on two rows

Author

Topic

DeNam
Starting Member

15 Posts

Posted - 2013-10-29 : 04:34:21

Hi,

How can i select row nr (2) from the following table?

I want to select
distinct ID over first MAX Valid to date and then Valid from date.

ID Rating Valid to Valid from
5562512136 12 06.07.2013 31.12.2013
5562512136 9 06.07.2013 31.12.9999
5562512136 7 06.12.2012 31.12.9999
5562512136 8 06.07.2013 31.12.2015

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-29 : 05:09:56

[code]
SELECT ID, Rating, Validto, Validfrom
FROM
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ValidTo DESC, ValidFrom DESC) AS Seq
FROM table
)t
WHERE Seq=1
[/code]
I assume Validto and Vlidfrom are of datetime datatype

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

DeNam
Starting Member

15 Posts

Posted - 2013-10-29 : 05:35:15

Great, Worked perfect thanks.

btw: How do i mark my topics as solved?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-10-29 : 08:12:05

You're welcome
Just append [Solved] to title of topic for that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources