Fanny V Fanny V - 19 days ago 7
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 :

Select ID, DOCUMENTATION, FIELDCODE FROM TABLE.


The result is :

ID DOCUMENTATION FIELDCODE
1 testDoc 0
1 NULL 202
2 NULL 202
3 NULL 202
4 testDoc2 0
4 NULL 202


However, I want to have :

ID DOCUMENTATION FIELDCODE
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.

Thanks,

Answer

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.

Comments