How to distinct with condition

I am working on T-SQL. I got a table like this.

x   y   z   t
1   avc 45353   11.02.2019
2   avc 24324   29.05.2019
3   frg 54647   30.06.2019
3   dsc 35678   29.05.2019
4   dec 21445   13.04.2019
4   dec 21445   19.04.2019
5   exc 24324   29.05.2019
6   xyz 37777   29.05.2019
7   xyz 37777   29.05.2019

I want to write a script that will give me only different “z” values for each “y” values. I can’t group by “y” only I got “Column ‘x’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.” error.

How can I fix this?

My outcome should be like this:

x   y   z   t
1   avc 45353   11.02.2019
2   avc 24324   29.05.2019
3   frg 54647   30.06.2019
3   dsc 35678   29.05.2019
4   dec 21445   13.04.2019
5   exc 24324   29.05.2019
6   xyz 37777   29.05.2019

Answer

You can use row_number()

select * from
(
select *, row_number() over(partition by y, z order by t) as rn
from tablename
)A where rn=1

Advertisement

Answer