Search code examples
sqlsql-serversubqueryexists

Filter records in a table based on some columns


I have one table (say , table name is "items") as given below.

id timestamp name amount
100 16106800 pqr 10
100 16106800 pqr 0
100 16106400 pqr 15

If there are more than one record with same name and same timestamp then I want to exclude one with zero amount. For example - In the above table , we have two records (first and second records) with same name and same timestamp, so I have to exclude one record with zero amount.

Expected result is as below:

id timestamp name amount
100 16106800 pqr 10
100 16106400 pqr 15

I wrote a query like below but that doesn't work completely.

SELECT *
FROM items w
WHERE EXISTS (
    SELECT *
    FROM items x  
    WHERE x.timestamp = w.timestamp AND x.name=w.name
) AND w.amount > 0

My above query getting failed when all the timestamps are different. If there is no records with same name and same timestamp then it will not exclude anything from items table.

EDIT : If there is only one row with zero amount in that table then it should not be excluded.


Solution

  • My query produces these results:

    If you have only one record despite the amount, it is showed

    If you have more than one record, it will show only the records with amount > 0

    So, try this:

    SELECT *
    FROM items t1
    WHERE NOT EXISTS 
        (SELECT 1 FROM items t2
        WHERE t1.id = t2.id 
        AND t1.timestamp = t2.timestamp 
        AND t1.name = t2.name
        AND t2.amount > 0)
    OR t1.amount > 0
    

    See SQL Fiddle

    Be aware: I've written on SQL Fiddle with a different DBMS but the SQL used is standard, so it's OK for SQL Server too.