CODEWITHSUNDEEP
sqlsql-server-2008selectview
Josef
Josef

Reputation: 2726

Removing duplicate results

I have a view with some records, many of them are duplicated. I need to filter records and get only one from each of them.

I've tried with

SELECT TOP 1 Item, Code, Desc, '1' AS Qty FROM vwTbl1 WHERE Code = '12' OR Code = '311'

Also tried with DISTINCT but still I get all records.

but in this case it shows me only one record. Grouping by Code doesn't work. Is there any other way how to solve this?

    Item | Code   | Desc | QTY  

 a       |   12   |  1   |1
 a       |   311  |  2   |1
 b       |   12   |  3   |1
 b       |   311  |  4   |1
 c       |   1    |  5   |1

Reult should be like: 

    Item | Code   | Desc | QTY  

 a       |   12   |  1   |1
 b       |   311  |  3   |1

So for each criteria get the first record.

Upvotes: 1

Views: 32

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270191

The typical way of doing this uses row_number():

SELECT TOP 1 Item, Code, Desc, 1 AS Qty
FROM (SELECT v.*,
             ROW_NUMBER() OVER (PARTITION BY Code ORDER BY (SELECT NULL)) as seqnum
      FROM vwTbl1 
      WHERE Code IN ('12', '311') -- don't use single quotes if these are numbers
     ) v
WHERE seqnum = 1;

Upvotes: 1

Mansoor
Mansoor

Reputation: 4192

SELECT Top 1 *
FROM 
(
  SELECT Item, Code, Desc, '1' AS Qty 
  FROM vwTbl1 WHERE Code = '12' OR Code ='311'
)A

Edited Code based on your expected result:

Declare @YourTable table (Id INT IDENTITY(1,1),Item varchar(50),Code INT,       
_Desc INT,Qty INT)
 Insert into @YourTable
 SELECT 'a',12,1,1 UNION ALL
 SELECT 'a',311,2,1 UNION ALL
 SELECT 'b',12,3,1 UNION ALL
 SELECT  'b',311,4,1 UNION ALL
 SELECT  'c',1 ,5 ,1   

 SELECT Item ,A.Code , _Desc ,Qty
 FROM @YourTable T
 JOIN 
 (
  SELECT MAX(Id) Id, Code FROM @YourTable GROUP BY Code
 )A ON A.Id = T.Id

Upvotes: 0

Related Questions