Select a row from similar rows MYSQL

user_15259340 0
Viewed 32

I have a Table like this

CREATE TABLE prova
    (`ID` int, `CODCLI` longtext, `RIFYEAR` int, `VAL` int)
;
    
INSERT INTO prova
    (`ID`, `CODCLI`, `RIFYEAR`, `VAL`)
VALUES
    (1, '1dad000', 2020, 150),
    (2, '500', 2020, 100),
    (3, '1dad000', 2021, 50),
    (4, '1dad000', 2022, 70),
    (5, '2000', 2023, 80)
;

http://www.sqlfiddle.com/#!9/7697a4/4 and i want to select these rows

2, '500', 2020, 100
3, '1dad000', 2021, 50
4, '1dad000', 2022, 70

how can i do?

i wrote something like this

SELECT * 
  FROM prova 
 WHERE CODCLI IN ('500','1dad000')

but when 'RIFYEAR' is same, i want to select only the row that has CODCLI = 500.

thanks for your help ;)

row where-clause select mysql sql
1 Answers

One method uses window function:

SELECT p.*
FROM (SELECT p.*,
             ROW_NUMBER() OVER (PARTITION BY year ORDER BY CODCLI DESC) as seqnum
      FROM prova p
      WHERE CODCLI IN ('500', '1dad000') 
     ) p
WHERE seqnum = 1;

This is guaranteed to return one row per year.

Or using NOT EXISTS:

select p.*
from prova p
where p.codcli = '500' or
      (p.codcli = '1dad000' and
       not exists (select 1
                   from prova p2
                   where p2.year = p.year and p2.codcli = '500'
                  )
      );

This can return duplicates per year if there are duplicates in the prova.

user_11440350
Related