I need select data in SQL Server. I have a Data example like this
x
DECLARE @tblA TABLE (ID int, SubId int, Createdate DateTime)INSERT INTO @tblA VALUES (1, 1, '10/21/2020') , (2, 1, '10/21/2020') , (3, 1, '10/27/2020') , (4, 2, '10/21/2020') , (5, 2, '10/21/2020') , (6, 1, '10/21/2020') , (7, 2, '10/23/2020') , (8, 2, '10/22/2020') , (9, 1, '10/25/2020') , (10, 3, '10/21/2020')I want to get 4 records (in table will have a lot of SubId) 2 records latest date by SubId = 1 2 records latest date by SubId = 2 Following example, I expect Select with like table Output
DECLARE @tblOutput Table (ID int, SubId int, Createdate DateTime)INSERT INTO @tblOutput VALUES (3, 1, '10/27/2020') , (9, 1, '10/25/2020') , (7, 2, '10/23/2020') , (8, 2, '10/22/2020')
“tooltip”
I try with Union but It’s only Order after Union. It’s not the result I want.
Please, Help me to select this. Thank you.
Advertisement
Answer
You can use the Row_Number() function as shown below:
Create table tblA (ID int, SubId int, Createdate DateTime)Insert Into tblA values (1, 1, '10/21/2020') , (2, 1, '10/21/2020') , (3, 1, '10/27/2020') , (4, 2, '10/21/2020') , (5, 2, '10/21/2020') , (6, 1, '10/21/2020') , (7, 2, '10/23/2020') , (8, 2, '10/22/2020') , (9, 1, '10/25/2020') , (10, 3, '10/21/2020')select Id , SubId , Createdatefrom( select * , Row_Number() Over (Partition By SubId order by Createdate desc) as RNo from tblA)temp where RNo <= 2 and SubId in (1, 2)Order by SubId