Select Top 1 with Ties Sample with Row_number or dense_rank


create table myTable (
machine_id int
, column_date date
, username varchar(50))

INSERT INTO MYTABLE values
(1,’2020-01-01′,’user1′)
,(1,’2020-03-01′,’user2′)
,(1,’2020-05-01′,’user3′)
,(1,’2020-05-01′,’user4′)

,(2,’2020-01-01′,’user4′),(2,’2020-04-01′,’user5′)
,(3,’2020-02-01′,’user6′),(3,’2020-03-01′,’user7′)

–most time use a row_number function to filter rn=1
;with mycte as (

SELECT machine_id , column_date, username
, ROW_NUMBER() OVER (PARTITION BY machine_id ORDER BY column_date DESC) as rn
FROM mytable
)

Select machine_id , column_date, username
from mycte
WHERE rn = 1 ;

–use row_number() in order by

— Post from Jeff Williams 3188
Select Top 1 With Ties
machine_id, column_date, username From myTable
Order By row_number() over(Partition By machine_id Order By column_date desc);
–use dense_rank to get the tie rows
Select Top 1 With Ties
machine_id, column_date, username From myTable
Order By dense_rank() over(Partition By machine_id Order By column_date desc);
drop table myTable

 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2eabc41f-f989-4f9b-917f-9515e31ea817/need-help-with-nested-sql-query-and-top-clause?forum=transactsql#2eabc41f-f989-4f9b-917f-9515e31ea817



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s