Multiple duplicates

Question

Post reply

Multiple duplicates

rvenkatesan

Valued Member

Points: 57
More actions
April 3, 2014 at 7:21 am

#306301

Pls help me
My Table looks like
Track | Books
-----------------
204 | A
204 | B
204 | B
204 | C
206 | A
204 | A
204 | A
203 | A
202 | C
202 | C
202 | B
I need a output like
Track | Books
-----------------
204 | A
204 | B
204 | C
202 | C
202 | B
pls someone help me

Viewing 15 posts - 1 through 15 (of 16 total)

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

djj SSCoach Points: 18831 More actions · Answer 1

djj

SSCoach

Points: 18831

April 3, 2014 at 7:39 am

#1702847

What have you tried?

SELECT DISTINCT ?

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Are you trying to dedupe the data in this table or just the result set?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

rvenkatesan Valued Member Points: 57 More actions · Answer 3

I no need the distinct , I need same tracks which have multiple books and I don't need tracks which have

unique books for(eg)

Track | Books

-----------------

204 | A

204 | B

204 | C

202 | C

202 | B

track 204 has multiple books and track 202 has multiple books ???

and i no need of the DISTINCT ...

thanks for the reply in advance..

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

This would do as you ask...

SELECT Track, Books

FROM dbo.YourTable

GROUP BY Track, Books

HAVING COUNT(*) > 1

ORDER BY Track, Books

;

--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)

djj SSCoach Points: 18831 More actions · Answer 5

djj

SSCoach

Points: 18831

April 3, 2014 at 7:58 am

#1702863

Sorry just reread your reply.

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

djj (4/3/2014)
Try running this:
create table #temp1 (Track INT, Books VARCHAR(10))
insert into #temp1 values
(204,'A'),
(204,'B'),
(204,'B'),
(204,'C'),
(206,'A'),
(204,'A'),
(204,'A'),
(203,'A'),
(202,'C'),
(202,'C'),
(202,'B')
SELECT Track, Books FROM #temp1
SELECT DISTINCT Track, Books FROM #temp1
order by 1, 2

Like the OP said, he's not looking for DISTINCT items... he's only looking for items that have more than one occurance.

Also, you should get out of the habit of using column ordinals in the ORDER BY. It was never a good practice and the method has been deprecated by Microsoft.

--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)

djj SSCoach Points: 18831 More actions · Answer 7

Yep I see that now. Again sorry for the misstep.

SQLRNNR SSC Guru Points: 281334 More actions · Answer 8

This should produce the results you have requested

DECLARE @table TABLE (Track INT, Books CHAR(1))

INSERT INTO @Table

VALUES (204,'A'),

(204,'B'),

(204,'C'),

(206,'A'),

(204,'A'),

(203,'A'),

(202,'C'),

(202,'B');

WITH presel AS (

SELECT Track

FROM @table

GROUP BY Track

HAVING COUNT(*) > 1

)

SELECT Track, Books

FROM @table t

WHERE Track IN (SELECT Track FROM presel)

GROUP BY Track, Books

ORDER BY Track DESC, Books ASC

;

here is the output from that

TrackBooks
204A
204B
204C
202B
202C

The one difference is the sort order you have prescribed in your desired output conflicts within itself. You have an ASC sort followed by a DESC sort within the Books portion of the sort. So I stuck with the ASC sort.

The other queries presented thus far fall a bit short in your request

Taking an example

DECLARE @table TABLE (Track INT, Books CHAR(1))

INSERT INTO @Table

VALUES (204,'A'),

(204,'B'),

(204,'C'),

(206,'A'),

(204,'A'),

(203,'A'),

(202,'C'),

(202,'B');

SELECT Track, Books

FROM @table t

GROUP BY Track, Books

HAVING COUNT(*) > 1

ORDER BY Track, Books

;

This produces

TrackBooks
202C
204A
204B

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

rvenkatesan Valued Member Points: 57 More actions · Answer 9

i will make clear,

I am looking for items that have more than one occurrence in track, in the above sample track 204 has 3 distinct Multiples and the track 202 has 2 distinct multiple ,i need a query to get this output

tks for replay in advance..

SQLRNNR SSC Guru Points: 281334 More actions · Answer 10

rvenkatesan (4/3/2014)
i will make clear,
I am looking for items that have more than one occurrence in track, in the above sample track 204 has 3 distinct Multiples and the track 202 has 2 distinct multiple ,i need a query to get this output
tks for replay in advance..

Did you even look at the query I provided? It does exactly what you requested.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

djj SSCoach Points: 18831 More actions · Answer 11

Guess I am not the only one not reading today. 🙂

SQLRNNR SSC Guru Points: 281334 More actions · Answer 12

djj (4/3/2014)
Guess I am not the only one not reading today. 🙂

:hehe::-D

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

rvenkatesan Valued Member Points: 57 More actions · Answer 13

tks for the query its working but it missed the

TrackBooks

202B

204C

pls help us?

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 14

djj (4/3/2014)
Yep I see that now. Again sorry for the misstep.

Nah... not a misstep. I was just making sure. It's good that other people post and you were trying. Thanks for that.

--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)