CODEWITHSUNDEEP
sqlsql-servert-sql
Cristen Rafalko
Cristen Rafalko

Reputation: 85

TSQL - Select Latest Date by 2 Type

I need select data in SQL Server. I have a Data example like this

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')

Example "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.

Upvotes: 0

Views: 52

Answers (1)

Suraj Kumar
Suraj Kumar

Reputation: 5643

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
 , Createdate
from(
  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

Live db<>fiddle demo

Upvotes: 3

Related Questions