CODEWITHSUNDEEP
sql-server
Doonie Darkoo
Doonie Darkoo

Reputation: 1495

How to show DISTINCT values Group Wise

I have following table structure along with the sample data. The question's required output is provided along with what it's now giving. Seems pretty simple but I'm not able to create a logic. Can anyone help ?

CREATE TABLE InvoiceReceipt
(
ID INT NOT NULL, 
InvoiceCode VARCHAR(25),
ReceiptAmount Numeric(18, 2)
)


INSERT INTO InvoiceReceipt VALUES (1, 'FS/1001/20', '2500');
INSERT INTO InvoiceReceipt VALUES (2, 'FS/1002/20', '2500');
INSERT INTO InvoiceReceipt VALUES (3, 'FS/1003/20', '4300');
INSERT INTO InvoiceReceipt VALUES (4, 'FS/1004/20', '2100');

SELECT * FROM InvoiceReceipt

Output:

ID  InvoiceCode ReceiptAmount 
1   FS/1001/20  2500.00
2   FS/1002/20  2500.00
3   FS/1003/20  4300.00
4   FS/1004/20  2100.00

Required Output:

ID  InvoiceCode ReceiptAmount 
1   FS/1001/20  2500.00
2   FS/1002/20  0
3   FS/1003/20  4300.00
4   FS/1004/20  2100.00

What I want to achieve is that if same amount is present for multiple invoices, it should only with the first one and 0 or null should be shown in following invoices. I don't want to omit the other rows so. Hope someone can help.

Upvotes: 0

Views: 67

Answers (3)

Ed Bangga
Ed Bangga

Reputation: 13026

you can use dense_rank() and select case for this requirement.

select t1.ID
    , t1.InvoiceCode
    , case when rn > 1 then 0 else t1.ReceiptAmount end  
from (
    select dense_rank() over (partition by ReceiptAmount order by id) as rn,* 
        from InvoiceReceipt) t1
left join InvoiceReceipt t2 on t2.id = t1.id
order by id asc

you can still be able to get this without using dense_rank() or row_number(), its more faster

select t1.id, t1.InvoiceCode, t1.ReceiptAmount
    , case when t2.ReceiptAmount=t1.ReceiptAmount and t1.id>t2.id then 0 else t1.ReceiptAmount end
from InvoiceReceipt t1
left join InvoiceReceipt t2 on t1.ReceiptAmount = t2.ReceiptAmount and t1.id!=t2.id

Upvotes: 1

Adnan Burhanpurwala
Adnan Burhanpurwala

Reputation: 21

You can use LAG function to compare value with the previous row;

select *,case when (ReceiptAmount = LAG(ReceiptAmount,1) over (order by ReceiptAmount)) 
THEN 0 else ReceiptAmount end
from InvoiceReceipt

Upvotes: 2

Zhorov
Zhorov

Reputation: 30023

If I understand you correctly, next approach may help:

SELECT 
    ID, 
    InvoiceCode, 
    CASE
        WHEN Rn = 1 THEN ReceiptAmount
        ELSE 0
    END AS ReceiptAmount
FROM (
    SELECT 
        *,
        ROW_NUMBER() OVER (PARTITION BY ReceiptAmount ORDER BY ID) AS Rn
    FROM InvoiceReceipt
) t
ORDER BY ID

Upvotes: 1

Related Questions