how to filter multiple rows in a sql table based on two columns

Question

ID     |Number|Qty|OrderNumber|Date
456-98A|746-96|0.1|00         |2015-01-01
456-98A|746-96|0.1|00         |2015-01-01
456-98A|746-96|0.1|00         |2015-01-01
456-98A|746-96|0.1|00         |2015-01-01
456-98A|321-96|0.3|05         |2015-01-01
456-98A|321-96|0.3|05         |2015-01-01

the above table in sql gives me multiple rows of the same ID.How do i filter this data so that if the Number and orderNumber is the same it must give me only one row for that ID instead of multiple values.for eg the expected outcome would be

ID     |Number|Qty|OrderNumber|Date
456-98A|746-96|0.1|00         |2015-01-01
456-98A|321-96|0.3|05         |2015-01-01

Just use "select distinct"? Without the actual SQL + table structure it's not really possible to know if there's something else wrong — James Z, Oct 12 '15 at 14:13

score 1 · Answer 1 · answered Oct 12 '15 at 14:16

1

You can group by column you need or all or distinct.

select ID, Number, Qty, OrderNumber, Date from YourTable
group by ID, Number, Qty, OrderNumber, Date

answered Oct 12 '15 at 14:16

Arkadiusz

489
2
10

ughai · Accepted Answer · 2015-10-12T14:42:57.317

You can use ROW_NUMBER() with PARTITION BY like this

SQL Fiddle

Query

SELECT ID, Number,Qty,OrderNumber,Date
FROM 
(
SELECT ID, Number,Qty,OrderNumber,Date,ROW_NUMBER()OVER(PARTITION BY Number, orderNumber ORDER BY ID ) as rn
FROM yourTable
)yourTable
WHERE rn = 1

Sample Data

CREATE TABLE yourTable
    ([ID] varchar(7), [Number] varchar(6), [Qty] int, [OrderNumber] int, [Date] datetime);

INSERT INTO yourTable
    ([ID], [Number], [Qty], [OrderNumber], [Date])
VALUES
    ('456-98A', '746-96', 0.1, 00, '2015-01-01 00:00:00'),
    ('456-98A', '746-96', 0.1, 00, '2015-01-01 00:00:00'),
    ('456-98A', '746-96', 0.1, 00, '2015-01-01 00:00:00'),
    ('456-98A', '746-96', 0.1, 00, '2015-01-01 00:00:00'),
    ('456-98A', '321-96', 0.3, 05, '2015-01-01 00:00:00'),
    ('456-98A', '321-96', 0.3, 05, '2015-01-01 00:00:00');

Output

ID  Number  Qty OrderNumber Date
456-98A 321-96  0   5   2015-01-01 00:00:00.000
456-98A 746-96  0   0   2015-01-01 00:00:00.000

score 0 · Answer 3 · answered Oct 12 '15 at 15:46

If you can use a temporary table, you can do :

CREATE TABLE #tempo
  ([rowNum] identity, [ID] varchar(7), [Number] varchar(6), [Qty] int, [OrderNumber] int, [Date] datetime); 

INSERT INTO #tempo ([ID], [Number], [Qty], [OrderNumber], [Date])
SELECT [ID], [Number], [Qty], [OrderNumber], [Date] FROM yourtable

And to get the right rows:

SELECT t1.[ID], t1.[Number], t1.[Qty], t1.[OrderNumber], t1.[Date]
FROM #tempo AS t1
WHERE NOT EXISTS (SELECT 1 
    FROM #tempo AS t2
    WHERE t2.ID = t1.ID 
      AND t2.Number = t2.Number 
      -- **and other columns if needed**    
      AND t2.RowNum > t1.RowNum)

how to filter multiple rows in a sql table based on two columns

3 Answers3