CODEWITHSUNDEEP
sqlarcgis
Riz_K
Riz_K

Reputation: 71

SQL query to find unique values

I need to write a query that truncates based on a selection and outputs another table. The selection criteria is as follows: for each common ID loop through the AGREE column to find a Y, if no Y then output 0, if a single Y then output that year, if multiple Y then output the most current year.

Input table:

ID  AGREE   YEAR
1   N   2003

2   Y   2005
2   N   2015

3   N   2005    
3   N   2007
3   Y   2011
3   Y   1999

4   N   2005
4   N   2010

Output table:

ID  AGREE   YEAR

1   N   0
2   Y   2005
3   Y   2011
4   N   0

Upvotes: 0

Views: 222

Answers (1)

TPhe
TPhe

Reputation: 1671

Here is my solution:

Select id, max(agree), max(case when agree = 'Y' then year else 0 end)
from [input table]
group by id

It rests on grouping by the id field and using max statements to return a "Y" if it is present for the group, and then return the largest number for year when agree is "Y". Note that you say "most recent" - if this table contains years in the future it would not return the most recent but instead the furthest into the future.

Note: There is an alternate way of doing this that is often faster that involves using sub-queries. If you run into performance issues it would be worth pursuing.

Upvotes: 2

Related Questions