CODEWITHSUNDEEP
sqlsql-serverselectduplicates
Ricky
Ricky

Reputation: 13

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

Upvotes: 1

Views: 1697

Answers (3)

sandeep rawat
sandeep rawat

Reputation: 4957

Use below code 

  ;with temp as (
            select id,name ,PO_NUMBER ,DATE, STATS,
            LAG (STATS, 1, 0) 
            OVER (PARTITION BY name ,PO_NUMBER ,DATE ORDER BY id) AS PrevSTATS  
            from tableName 
    )
       select id,name ,PO_NUMBER ,DATE,
        case when  STATS = PrevSTATS then null 
               else     STATS end as STATS  
    from temp

Upvotes: 0

sgeddes
sgeddes

Reputation: 62831

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

Upvotes: 2

Squirrel
Squirrel

Reputation: 24763

since you are using SQL 2012, you can use the LEAD() or LAG() window function to compare the DATE value

select  *,
        STATS = case when t.DATE = LAG(DATE) OVER(ORDER BY ID) 
                     then NULL 
                     else STATS 
                     end
from    yourtable t

Upvotes: 0

Related Questions