Fanny V Fanny V - 1 year ago 54
SQL Question

How can I select lines with specific data when I have 2 lines with the same ID?

I need your help again !
I'm trying to have a specific result in SQL
I have a SQL Query like :


The result is :

1 testDoc 0
1 NULL 202
2 NULL 202
3 NULL 202
4 testDoc2 0
4 NULL 202

However, I want to have :

1 testDoc 0
2 NULL 202
3 NULL 202
4 testDoc2 0

To sum up :

  • I want to have the fieldcode 0 when it exist

  • If the fieldcode 0 isn't exist, I want the fieldcode 202

  • The fieldcode id will always be 202

  • the documentation for the 202 fieldcode will be always set to NULL

  • the fieldcode with a documentation will always be 0

  • I'm building the documentation with a case : if the fieldcode is 202 -> set NULL else print the good documentation.

My problem is the documentation. The documentation is not the same. I cannot use MIN...

I did not find solution yet.


Answer Source

One method uses row_number():

select t.*
from (select t.*,
             row_number() over (partition by id order by fieldcode) as seqnum
      from t
     ) t
where seqnum = 1;

This is an example of a prioritization query, where you are trying to pick among several rows for the same id. row_number() is often the simplest way to approach these queries.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download