SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Choose rows depended on two rows
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

DeNam
Starting Member

Sweden
15 Posts

Posted - 10/29/2013 :  04:34:21  Show Profile  Reply with Quote
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

India
52317 Posts

Posted - 10/29/2013 :  05:09:56  Show Profile  Reply with Quote

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

I assume Validto and Vlidfrom are of datetime datatype

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

DeNam
Starting Member

Sweden
15 Posts

Posted - 10/29/2013 :  05:35:15  Show Profile  Reply with Quote
Great, Worked perfect thanks.

btw: How do i mark my topics as solved?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/29/2013 :  08:12:05  Show Profile  Reply with Quote
You're welcome
Just append [Solved] to title of topic for that

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000