Ricky Ricky - 2 months ago 7
SQL Question

Show only one record, if value same in another column SQL

I have a table with 5 columns like this:


| ID | NAME | PO_NUMBER | DATE | STATS |
| 1 | Jhon | 160101-001 | 2016-01-01 | 7 |
| 2 | Jhon | 160101-002 | 2016-01-01 | 7 |
| 3 | Jhon | 160102-001 | 2016-01-02 | 7 |
| 4 | Jane | 160101-001 | 2016-01-01 | 7 |
| 5 | Jane | 160102-001 | 2016-01-02 | 7 |
| 6 | Jane | 160102-002 | 2016-01-02 | 7 |
| 7 | Jane | 160102-003 | 2016-01-02 | 7 |


I need to display all values, but
stats
fields without duplicate according from
date
field.

Like this


| ID | NAME | PO_NUMBER | DATE | STATS |
| 1 | Jhon | 160101-001 | 2016-01-01 | 7 |
| 2 | Jhon | 160101-002 | 2016-01-01 | null |
| 3 | Jhon | 160102-001 | 2016-01-02 | 7 |
| 4 | Jane | 160101-001 | 2016-01-01 | 7 |
| 5 | Jane | 160102-001 | 2016-01-02 | 7 |
| 6 | Jane | 160102-002 | 2016-01-02 | null |
| 7 | Jane | 160102-003 | 2016-01-02 | null |


I've had trouble getting the hoped. Thanks

Answer

From your sample data, it appears you only want to show the stats for po_number ending with 001. If so, this should be the easiest approach:

select id, name, po_number, date, 
       case when right(po_number, 3) = '001' then stats else null end as stats
from yourtable 

If instead you want to order by the po_number, then here's one option using row_number:

select id, name, po_number, date, 
       case when rn = 1 then stats else null end as stats
from (
     select *, row_number() over (partition by name, date order by po_number) as rn
     from yourtable
) t