SajjadZare SajjadZare - 6 months ago 11
SQL Question

Query for this table

How to write query for below table that give below result , means when a person has record for year 93 just show it but if hasn't record give year 92

Table :
enter image description here

Result :
enter image description here

Answer

You can use ROW_NUMBER() with CASE EXPRESSION :

SELECT t.code,t.name,t.year
FROM (SELECT s.*,ROW_NUMBER() OVER (PARTITION BY s.name
                                    ORDER BY CASE WHEN s.year = 92 then 1
                                                  WHEN s.year = 93 then 2
                                                  ELSE 3
                                             END) as rnk
      FROM YourTable s) t
WHERE t.rnk = 1

I also took the cases when there is more years then 92,93 (prioritized last)