Distinct SQL Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Distinct SQL Question

Author

Topic

Faiyth
Starting Member

19 Posts

Posted - 2002-12-26 : 14:58:57

I am trying to display a list of all the books we carry. But I don't want to show the same book twice. I have a list of 5 books, 2 of which are the same. I want to display ALL of the books that do not have the same title along with all the corrosponding data. I'm allowing the user to enter in books as well, so my Distict has to hide all duplicates that staff might enter in. If we get 2 more copies of Cape, or Lilies, it needs to hide those too.

Here's my data:
ISBN - Book Title - Author - Cost
100 - Apes - Abe, Bab - $10
101 - Babe - Bab, Abe - $11
102 - Cape - Sum, Pum - $12
103 - Apes - Rad, Sad - $10
104 - Lilies - Ead, Pead - $14

What I want it to look like is this:
ISBN Book Title Author Cost
100 - Apes - Abe, Bab - $10
101 - Babe - Bab, Abe - $11
102 - Cape - Sum, Pum - $12
104 - Lilies - Ead, Pead - $14

But remember that someone can come along and add a duplicate book named Babe, so it would have to hide that one too.

I'm trying this right now: And it's displaying everything. Even duplicates.

select N.* from isbooks N where N.isbooktitle IN (Select ST.isbooktitle from isbooks ST where (ST.isbooktitle = N.isbooktitle))

How do I put distinct JUST on Isbooktitle, so that it will only display one copy of that rather then every copy we have?

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-26 : 15:26:11

Be sure to change field2, field3 etc to all the fields in your table.
You should NOT use *. List out only the fields of your table that you need. It runs faster, and returns faster since there should be less data.


SELECT DISTINCT BookTitle, field2, field3
FROM isbooks

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Faiyth
Starting Member

19 Posts

Posted - 2002-12-26 : 16:39:05

I get the same thing.. except the 2 fields that are the same return in alphabetical order by book title.. The two book titles are exactly the same.. So that doesn't work.

This is what I'm getting:
ISBN - Book Title - Author - Cost
100 - Apes - Abe, Bab - $10
103 - Apes - Rad, Sad - $10
101 - Babe - Bab, Abe - $11
102 - Cape - Sum, Pum - $12
104 - Lilies - Ead, Pead - $14

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-26 : 16:55:30

Hmm, this IS a tough one, for me at least.

What is your Primary key for this table? ISBN?

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-26 : 17:00:07

How about we just delete the duplicates? There are plenty of scripts around to do that!

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

Faiyth
Starting Member

19 Posts

Posted - 2002-12-26 : 17:00:20

Yes ISBN is my primary key and all the books have different numbers.

I've opened up my tables and looked over the results to be sure they are all the same and they are. They have the same structure no extra spaces... I've tried to Group By Booktitle and it doesn't work, It just returns "Unable to Query isbooks table".

Faiyth
Starting Member

19 Posts

Posted - 2002-12-26 : 17:05:09

Yeah I wish it was as easy as just deleting the ones that are duplicates. But I want duplicates in the table, I don't want them to display when I'm showing our inventory though, but they need to be there.

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-12-26 : 17:09:43

I have a solution!
It's pretty ugly and slow, but it works with your very odd data and requirements. I really suggest not allowing your users to enter "duplicate data" into the system in the first place. it will amek you life MUCH easier!


CREATE TABLE #Books(ISBN INT, BookTitle VARCHAR(50), Author VARCHAR(50), Cost MONEY)

INSERT INTO #Books(ISBN, BookTitle, Author, Cost) VALUES(100, 'Apes', 'Abe, Bab', $10)
INSERT INTO #Books(ISBN, BookTitle, Author, Cost) VALUES(103, 'Apes', 'Rad, Sad', $10)
INSERT INTO #Books(ISBN, BookTitle, Author, Cost) VALUES(101 , 'Babe', 'Bab, Abe ', $11)
INSERT INTO #Books(ISBN, BookTitle, Author, Cost) VALUES(102, 'Cape', 'Sum, Pum', $10)
INSERT INTO #Books(ISBN, BookTitle, Author, Cost) VALUES(104, 'Lilies', 'Ead, Pead', $10)

SELECT DISTINCT BookTitle, 
(SELECT TOP 1 b2.ISBN FROM #Books b2 WHERE b2.BookTitle = b1.BookTitle) as ISBN,
(SELECT TOP 1 b2.Author FROM #Books b2 WHERE b2.BookTitle = b1.BookTitle) as Author,
(SELECT TOP 1 b2.Cost FROM #Books b2 WHERE b2.BookTitle = b1.BookTitle) as Cost
FROM #Books b1


DROP TABLE #Books

Michael

<Yoda>Use the Search page you must. Find the answer you will.</Yoda>

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2002-12-26 : 18:39:38

Wait -- why can't you say:

SELECT BookTitle
FROM
isBooks
GROUP BY BookTitle

??

Then you can decide if you want the MIN() or MAX() of all the other fields, or you can take the MIN of ISBN and use that as your primary book to return:

SELECT B.*
FROM isBooks B
INNER JOIN
(SELECT BookTitle, MIN(ISBN) as MinISBM FROM isBooks GROUP BY BookTitle) A
ON A.BookTitle = B.BookTitle AND
A.MinISBM = B.ISBM

That is the standard way to do these things ... is BookTitle a TEXT field, so things of this nature aren't working? Then just convert it to a VARCHAR(100) or something like that.

- Jeff

Edited by - jsmith8858 on 12/26/2002 20:36:52

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources