Question 264717 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Select one row per ID based on two columns

This is probably really simple, but I’ve been trying to figure this out for a while, maybe a fresh pair of eyes will/could help.

http://sqlfiddle.com/#!9/557e5a/1

I have this table wherein I need to prioritize selecting Accounts that are Chequing first, and if they don’t have a Chequing account, then select the Savings account.

As you can see, PartyID C only has a Savings account, and everyone else has either both, or only a Chequing account.

I tried SELECT PartyID, MIN(Description), MIN(Number) FROM Table GROUP By PartyID

and that gave me what I needed for the most part, but for PartyID E, it’s returning the Business Savings account instead of their Chequing account.

enter image description here

The desired output result would be this

enter image description here

Any help would be great.

Tags:

2

Answers


  1. 0

    Use a window function to rank the rows within each party ID, ranking Chequing before Savings.

    with ranked AS (
  SELECT *, 
    ROW_NUMBER() OVER (PARTITION BY PartyID ORDER by Classification = 'Chequing' DESC, Number) AS rn
  FROM Table1
)

SELECT PartyID, Classification, Description, Number
FROM ranked
WHERE rn = 1
ORDER BY PartyID

    DEMO

    Login or Signup to reply.
  2. 0

    Using analytic function row_number (note that analytic functions are supported from MySQL version 8.0 onwards):

    select 
    PartyID, 
    Description, 
    Classification, 
    Number 
from (
  select *, row_number() over(partition by PartyID 
                              order by 
                                case Classification when 'Checking' then 1 when 'Savings' then 2 end,
                                Number desc
                             ) as rn 
  from Table1
) as tab where rn = 1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search