-1

I have, for example, the following table:

Client ID Function
1234 RE
1234 WE
1234 SP
1234 AG
6789 AG
6789 WE

And I want to get only 1 client ID but that obeys the following order/hierarchy: If the Client ID has Function = 'SP' then I want to get this option first, if it has AG then second, the others are indifferent to the order.

Desired Output:

Client ID Function
1234 SP
6789 AG

How can I reproduce this in a query? Thanks

    WITH cte AS 
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY [CLIENT_ID]  ORDER BY FUNCTION ASC) AS rn
                ,[CLIENT_ID]
                , FUNCTION 
    FROM ope.stg_client
    ORDER BY (case when FUNCTION = 'SP' then 1 when FUNCTION = 'AG' then 1  ELSE 2  end) ASC OFFSET 0 ROWS
)
SELECT *
    FROM cte
    WHERE rn = 1

Output:

Client ID Function
1234 AG
6789 AG
Dory
  • 19
  • 5
  • Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) – Thom A Apr 29 '22 at 08:52
  • @Larnu no, that's what I tried but then the hierarchy that I want is not applied – Dory Apr 29 '22 at 09:02
  • So include that attempt so we can explain why it didn't work. – Thom A Apr 29 '22 at 09:03
  • I included my attempt @Larnu, thanks – Dory Apr 29 '22 at 09:37
  • you are almost there, you built a custom order with your case in the ORDER BY clause but it should actually be in the windowed function, `ROW_NUMBER() OVER(PARTITION BY [CLIENT_ID] ORDER BY CASE WHEN FUNCTION = 'SP' THEN 1 WHEN FUNCTION = 'AG' THEN 2 ELSE 3 END ASC)` – Ockert Apr 29 '22 at 09:55
  • @Ockert Yes, I realized that a little later. Thanks for the feedback! – Dory Apr 29 '22 at 09:57

1 Answers1

0

By doing

    WITH cte AS 
(
    SELECT
        ROW_NUMBER() OVER(PARTITION BY [CLIENT_ID] ORDER BY (
                CASE FUNCTION
                    WHEN 'SP' THEN 0
                    WHEN 'AG' THEN 1
                    ELSE 2
                END) ASC) AS rn
                ,[CLIENT_ID]
                , FUNCTION 
    FROM ope.stg_client
)
SELECT *
    FROM cte
    WHERE rn = 1

I was able to get the desire output

Dory
  • 19
  • 5