CODEWITHSUNDEEP
sqlsql-serverdatabase
General Electric
General Electric

Reputation: 1226

Select DISTINCT, TOP, ORDER BY

I need to get the top N rows with a different biAccountId value in order to avoid duplicates. I've tried to create a sub-query but SQL Aerver is telling me the ORDER BY clause can't be in sub-queries.

Here is the query I have so far. 

SELECT u.*, c.vcAccountID,t.tiAccountTypeID
FROM dbo.tbUserReviewAccount u
inner join tbCustAccount c on c.biAccountID=u.biAccountId
inner join tbAccountType t on t.tiAccountTypeID=c.tiAccountTypeID
where u.iUserId=1
order by u.dtReviewedOn desc

and here is the result I'm getting which I need to filter down to get the TOP N rows that matches the criteria which is to latest account Reviewed by the user, the date of review is on the column dtReviewedOn, but I need to get the TOP N distinct rows.

enter image description here

What I'm trying to get is only the rows 1, 4,5,6,7 if N=5 (N=TOP rows).

Upvotes: 0

Views: 1166

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269643

This would normally be handled using row_number():

select top (5) u.*
from (select u.*, c.vcAccountID, t.tiAccountTypeID,
             row_number() over (partition by u.biAccountId order by u.dtReviewdOn desc
      from dbo.tbUserReviewAccount u inner join
           tbCustAccount c
           on c.biAccountID = u.biAccountId join
           tbAccountType t on t.tiAccountTypeID=c.tiAccountTypeID
      where u.iUserId = 1
     ) u
where seqnum = 1
order by u.dtReviewedOn desc;

Upvotes: 2

Related Questions