CODEWITHSUNDEEP
sqlsql-serverssms
Tyler
Tyler

Reputation: 13

SQL statement for minimum time and minimum ID?

I'm really new to SQL, and I've encountered the problem as shown below:

https://i.sstatic.net/AJkmv.png

Imagine this person has made payments at the respective timings, with the respective IDs.

I would like to find out the amount of the earliest payment. If there are multiple entries for the earliest date, I would want to choose the entry with the smallest payment ID.

In this image the answer is simple, but what if the earliest payment was done on 5 Apr?

After removing the blank spaces for the titles, and after searching high and low for the answers, I wrote this:

SELECT PaymentID, PaymentAmt 
FROM 
( 
    SELECT 
        MIN(s.PaymentID), 
        s.PaymentDate, 
        s.PaymentAmt, 
        ROW_NUMBER() OVER(PARTITION BY s.PaymentID ORDER BY s.PaymentDate) rn 
    FROM sheetName s 
    GROUP BY s.userid, s.PaymentDate, s.PaymentAmt
) t 
WHERE rn = 1;

And it still doesn't work.

Could anyone help me with this?

EDIT: As written in my reply to Mark, what if I am looking into a group of people instead? So there could be another person with a different "userID" with their set of payment IDs and etc. If I want to find out the earliest payment amount for each person, what would be a good solution?

Upvotes: 1

Views: 105

Answers (3)

Lee Mac
Lee Mac

Reputation: 16015

For multiple users, perhaps something like this - 

select s.userid, s.paymentid, s.paymentdate, s.paymentamt
from sheetname s inner join
(
    select t.userid, min(t.paymentid) as minid
    from sheetname t inner join 
    (
        select u.userid, min(u.paymentdate) as mindate
        from sheetname u
        group by u.userid
    ) v on t.userid = v.userid and t.paymentdate = v.mindate
    group by t.userid
) w on s.userid = w.userid and s.paymentid = w.minid

Upvotes: 0

Mark Schultheiss
Mark Schultheiss

Reputation: 34197

Why make it complicated?

SELECT TOP 1 PaymentID, PaymentAmt
FROM sheetName
ORDER BY s.PaymentDate ASC, s.PaymentID ASC

Upvotes: 4

Squirrel
Squirrel

Reputation: 24793

just use row_number() with order by PaymentDate, PaymentID

select *
from
(
    select *, rn = row_number() over (order by PaymentDate, PaymentID)
    from   sheetName
) d
where d.rn = 1

Upvotes: 0

Related Questions