CODEWITHSUNDEEP
sqlsql-server
Massey
Massey

Reputation: 1125

How to get the desired row out of multiple rows based upon a column value in sql by exclusion

My sql query potentially can pull 1 or 2 rows which differ in the value of the SourceType column as well as CreatedDate column. The possible values of the SourceType column are "New" or "Old". If only one row containing "Old" is present, I want to get that row. If two rows are present, I want to get the row with the value "New". I could have done this by ordering the rows by CreatedDate and get the top 1, but I would like to use the SourceType to get the required row. I am not sure whether coalesce will work here or not.

Upvotes: 0

Views: 39

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can do this using prioritization logic. It looks like this:

select t.*
from t
where t.sourcetype = 'New'
union all
select t.*
from t
where t.soucetype = 'Old' and
      not exists (select 1 from t t2 where t2.? = t.? and t2.sourcetype = 'New');

The ? is for the column that specifies the duplicates.

Actually, the above logic will evaluate the subquery three times. That is okay for a single table. But there is actually a better way:

select q.*
from (select q.*,
             row_number() over (partition by ? order by sourceType) as seqnum
      from query q
     ) q
where seqnum = 1;

The ? is the id to identify multiple rows.

The order by sourceType is really la shorthand for order by case when sourceType = 'New' then 1 else 2 end.

Upvotes: 1

Related Questions