Conditional selection in SQL

Question

suppose we've got the following table:

A       B        C      Year
-----   ----    ----    -----
ABC     Val1   Val2     2015 
ABC     Val3   Val4     2016
ABC     Val6   Val7     2017 
ABC     Val8   Val9     2018
XYZ     Val10  Val9     2016 
XYZ     Val3   Val4     2017
WRD     Val1   Val2     2015
WRD     Va2    Val9     2017
----------------------------

As you can see the relationship between coloumns A and Year is 1:n The question: I can't figure it out, how to write a SQL query that prints out the values of the cloumns of A,B,C and D considering the following condition: if the corresponding year of the coloumn A is 2018 print that line (A,B,C,Year) other wise print the line of max(year). That means the answear should be the following:

A       B        C      Year
-----   ----    ----    -----
ABC     Val8   Val9     2018
XYZ     Val3   Val4     2017
WRD     Va2    Val9     2017

Many thanks in advance

Which [DBMS product](https://en.wikipedia.org/wiki/DBMS) are you using? "SQL" is just a query language, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using `postgresql`, `oracle`, `sql-server`, `db2`, ... — , Jul 27 '18 at 13:29

score 1 · Answer 1 · answered Jul 27 '18 at 13:07

1

Your sample data suggests :

select t.*
from table t
where year = (select max(t1.year) from table t1 where t1.a = t.a);

answered Jul 27 '18 at 13:07

Yogesh Sharma

49,870
5
26
52

Thank you Yogesh ,that washelpful. – RAKY Jul 30 '18 at 10:43

score 1 · Answer 2 · answered Jul 27 '18 at 13:54

For the sake of completeness, I will assume that your data could contain dates > 2018 and you still want to choose 2018; in which case:

SELECT *
FROM testdata
WHERE Year = (
  SELECT COALESCE(MAX(CASE WHEN Year = 2018 THEN Year END), MAX(Year))
  FROM testdata t1
  WHERE A = testdata.A
)

Conditional selection in SQL

2 Answers2