Query Request

Question

Query Request

Zahran

SSCommitted

Points: 1724
More actions
May 6, 2008 at 11:29 pm

#190295

[font="Courier New"]DECLARE @TABLE TABLE
(
EmpID CHAR(3)
,Category CHAR(1)
)
INSERT INTO @TABLE VALUES('101','A')
INSERT INTO @TABLE VALUES('102','B')
INSERT INTO @TABLE VALUES('103','C')
INSERT INTO @TABLE VALUES('104','A')
INSERT INTO @TABLE VALUES('105','B')
INSERT INTO @TABLE VALUES('106','C')
INSERT INTO @TABLE VALUES('107','D')
INSERT INTO @TABLE VALUES('108','A')
INSERT INTO @TABLE VALUES('109','A')
INSERT INTO @TABLE VALUES('110','D')[/font]
[font="Tahoma"]I need to remove record from each duplicated values in [Category] field.
Ex : One record from each A,B,C and D. Any Idea?[/font]
- Zahran -

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 1

Let's talk about just the ones for Category "A"... which one do you want to keep? The one with the lowest value EmpID or the highest?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Zahran SSCommitted Points: 1724 More actions · Answer 2

[font="Tahoma"]I want to remove the lowest value in each category[/font]

- Zahran -

Zahran SSCommitted Points: 1724 More actions · Answer 3

I use following query to remove those records. Problem solved.

[font="Courier New"]DELETE FROM @TABLE

WHERE EmpID IN(SELECT MIN(EmpID) AS EmpID FROM @TABLE GROUP BY Category)[/font]

- Zahran -

Jeff Moden SSC Guru Points: 1004365 More actions · Answer 4

You do realize that code will remove all single rows, correct?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 5

delete @Table

from @Table t

left join (

select Max(EmpID) as MaxID, Category

from @Table

group by Category

) t2

on t.EmpID = t2.MaxID

where t2.MaxId is null;

-- Check results

select *

from @Table;

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms