Skip to content
Advertisement

How to use only one query (no sub query) to get the rows which mach the requirement?

I need some suggestions for my case. First, I have a table (T1) like this:

I want to have a table like this:

The issue is, if I can use only one query (without any sub query), what I can do? I tried like this:

Select * case
when Flag = 0 then ‘F’
when Flag = 1 then ‘T’ End as Result From T1;

But for the ID = a, if i used this query, the result is not right.
I know, i use ‘With’ or something else with a sub query (one moere ‘select..’), the problem can be easily solved. So, what if i can use only one ‘select…’, any suggestions?

Advertisement

Answer

I think you want window functions to compare all the values for a given id. The following returns 0/1 (which makes sense to me:

For Y/N:

Note: This assumes that flag is never NULL, which is consistent with your data.

User contributions licensed under: CC BY-SA
4 People found this is helpful
Advertisement