SQL One Record Based on another row

Question

Apologies if this has been asked, I have had a look. I "learnt" SQL in March, by that I went on a one day course.

I current have a set of data that when extracts looks like

Pay     Ref AHRA ABA DATE           ANC DESCRIPTION SEQUENCE NUMBER
12345   4/3/2022 12:00:00 AM    General Needs accounts  50
12345   4/3/2022 12:00:00 AM    Working Age Claimants   45
12345   4/3/2022 12:00:00 AM    All Accounts           40
12345   4/3/2022 12:00:00 AM    No Benefits Direct  35
12345   4/3/2022 12:00:00 AM    Current Rent Accounts   30

What I would to extract from SQL Developer is the above where the Sequence Number is the lowest. From the above I would want

12345   4/3/2022 12:00:00 AM    Current Rent Accounts   30 

So for each Pay Ref, I want to extract the pay ref but only with the minimum sequence number. Hoping that that makes sense.

Thanks.

Answer 1

I think the MIN() function is what you're looking for and you'll want to use GROUP BY to get the lowest one for each Pay Ref:

SELECT "Pay Ref", 
       MIN("SEQUENCE NUMBER") AS MinSequenceNumber
FROM #payref
GROUP BY "Pay Ref";

That will get you the lowest sequence number for each Pay Ref in your table

If you want to get the full row for each pay ref with the lowest sequence number you can join like so:

SELECT pr1.*
FROM #payref AS pr1
     INNER JOIN
(
    SELECT "Pay Ref", 
           MIN("SEQUENCE NUMBER") AS MinSequenceNumber
    FROM #payref
    GROUP BY "Pay Ref"
) AS pr2 ON pr1."SEQUENCE NUMBER" = pr2.MinSequenceNumber;