CODEWITHSUNDEEP
sqlsql-server
Sadat Mainuddin
Sadat Mainuddin

Reputation: 309

SQL Server select first instance of ranked data with a twist

I have a Table1 that look like this:

Wallet No   Status      Rank 
01774076563 Scanned     1
01765653275 Registered  3
01774076563 Accepted    2
01768836816 Rejected    4
01967866981 Accepted    2
01967866981 Rejected    4

I want it to look like this (Table2): 

Wallet No   Status      Rank 
01774076563 Scanned     1
01765653275 Registered  3
01768836816 Rejected    4
01967866981 Accepted    2

I have used following code but it shows Table1 instead of Table2:

SELECT MIN([Rank]) AS [Rank], STATUS_, walletno into table2
FROM table1
GROUP BY STATUS_, walletno

What I want is only the highest ranking wallet no and status, nothing else. But the Table2 gives me exact copy of Table1. What am I doing wrong here?

Upvotes: 0

Views: 335

Answers (2)

Learner
Learner

Reputation: 4004

You can try this:

;WITH CTE as
(
SELECT
    WalletNo,
    Status,
    ROW_NUMBER() OVER(PARTITION BY Status ORDER BY Rank) AS Row,
    Rank
    FROM
    Item
)
SELECT WalletNo, Status, Row, Rank From CTE Where Row = 1

Fiddle here: http://sqlfiddle.com/#!3/14fa5/6

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270431

You are looking for the complete row, rather than just the minimum value. For this, the window functions are handy.

Because your logic uses min(), here is a method using that function:

SELECT [Rank], STATUS_, walletno into table2
FROM (select t.*,
             MIN([rank]) over (partition by walletno) as minrank
      from table1 t
     ) t
where [rank] = minrank;

The subquery calculates the minimum rank for each wallet. The outer filter than keeps only that row.

I would normally write this with row_number() as:

SELECT [Rank], STATUS_, walletno into table2
FROM (select t.*,
             row_number() over (partition by walletno order by [rank]) as seqnum
      from table1 t
     ) t
where seqnum = 1;

But the two should be pretty similar in terms of performance. The major difference is when two rows have the same minimum rank. The first version will return both rows. The second will arbitrarily choose one of them.

Upvotes: 1

Related Questions