SELECT all data where one column is distinct? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

Transact-SQL (2000)

SELECT all data where one column is distinct?

Author

Topic

dansqlteam
Starting Member

9 Posts

Posted - 2004-06-13 : 11:05:08

Hi,

I have a database with lots of rows. I want to return all data but only with a distinct "Title" value.

I've tried:

SELECT DISTINCT Title, * FROM etc etc...

But that doesn't seem to work.

Any help would be greatly appreciated, thanks.

gpl
Posting Yak Master

195 Posts

Posted - 2004-06-13 : 12:07:29

Which one of the rows for each unique title do you want ?
the way you have it, it will return all the distinct whole rows ....

post your table structure and a clearer idea of what you expect to return, that is, sample row values and what you expect to come out

Graham

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-13 : 12:23:02

Like Graham said, you'll have to get the rows down to a DISTINCT row before it will work. You could try getting the MAX(row) for each title and joining that back to your result set, but we can't tell what you really want without seeing table structure and an example.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

dansqlteam
Starting Member

9 Posts

Posted - 2004-06-13 : 17:44:26

Let me explain a bit more:

The database contains the following headers:

Id, Title, Description, Manufacturer, Price

Some of the products have the same title but different manufacturer. Basically, I need to return all of the data for a web listing, but only where titles are unique (the title can then be clicked to find the different manufacturers for this title). The results have to be ordered by title, and where there is more than one similar title, the results must show the product of minimum price - so that I can put "Prices start at...".

Does this make sense, and can anyone help?? Thanks.

gpl
Posting Yak Master

195 Posts

Posted - 2004-06-13 : 18:12:19

Aha
You really are looking at 2 queries -
1] the distinct list of Titles (and descriptions)
2] the list of manufacturers and their prices, for a particular title.

1] is quite simple
Select Distinct Title, Description from <table>
Order by Title
2] requires a parameter, the title (lets call it @Title)
Select Top 1 Title, Description, Manufacturer, Price from <table>
Where Title = @Title
Order by Price Asc

dansqlteam
Starting Member

9 Posts

Posted - 2004-06-13 : 18:17:57

If I do a "SELECT DISTINCT Title, Description" though, it selects only that data with distinct Title *and* Descriptions. I only need the Title column to be distinct.

I need to select ALL data, but in such a way that there aren't duplicate records of the same title.

Does that make sense??

Thanks...

gpl
Posting Yak Master

195 Posts

Posted - 2004-06-13 : 18:28:07

well if the descriptions are different, just return the title

the idea if the second query is for when the the required title has been clicked on, as you mentioned in your previous post.
The second query uses top 1 to select only the first matching row with your title
Graham

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-06-13 : 18:30:39

SELECT DISTINCT p1.Title, p1.Description, p1.Price, Counter
FROM
products p1
INNER JOIN (
SELECT Title, MIN(Price) AS Price, COUNT(Id) AS Counter
FROM products) p2 ON p1.Title = p2.Title
AND p1.Price = p2.Price

If the counter > 1, allow a query to select from products where Title = @Title. Is that what you are looking for?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

dansqlteam
Starting Member

9 Posts

Posted - 2004-06-13 : 18:33:15

If I just do a "SELECT DISTINCT Title", then the other data is n't returned.

I need all data to be returned, but only where the values for Title are different...

Any ideas?

dansqlteam
Starting Member

9 Posts

Posted - 2004-06-13 : 18:43:50

I'm almost there with the following:

SELECT Title, Min(Price), Description, Manufacturer FROM Products GROUP BY Title

But that throws errors.

The following *doesn't* error:

SELECT Title, Min(Price), Min(Description), Min(Manufacturer) FROM Products GROUP BY Title

But it won't return any values in my recordset other than title?? (Gives an "Item cannot be found in the collection corresponding to the requested name or ordinal." error).

So close yet so far!!

PS. I don't know why I put Min() around everything, it just made it work to a point. Is there something else I could put round the 'Description' and 'Manufacturer' selectors to enable them to be returned in the recordset?

dansqlteam
Starting Member

9 Posts

Posted - 2004-06-13 : 18:48:33

DerrickLeggett, if I do a "SELECT DISTINCT p1.Title, p1.Description, p1.Price, Counter" won't that select all distinct records, not just those with distinct title?

gpl
Posting Yak Master

195 Posts

Posted - 2004-06-13 : 18:49:04

quote:
Originally posted by dansqlteam

If I just do a "SELECT DISTINCT Title", then the other data is n't returned.

I need all data to be returned, but only where the values for Title are different...

Any ideas?

I think Ive got you now,try this


Select Title, Description, Manufacturer, Price, 
Counter = (Select Count(*) 
    From <table> t2 
    where t2.title = t2.title and t2.price < t1.price)
from <table> t1
Where (Select Count(*) 
    From <table> t2 
    where t2.title = t2.title and t2.price < t1.price) = 0
Order by Title Asc

Graham

ps, sorry did it wrong, changes in red

dansqlteam
Starting Member

9 Posts

Posted - 2004-06-13 : 19:00:26

Hmmm...

Error: "Too few parameters. Expected 4." ?

gpl
Posting Yak Master

195 Posts

Posted - 2004-06-13 : 19:04:51

what line did it error on - and whose code (if mine, did you take note of the change I just posted ?)
Graham

dansqlteam
Starting Member

9 Posts

Posted - 2004-06-13 : 19:09:42

I'm running this SQL query from an ASP page. It errors on the line of code that executes the query - even with those changes.

gpl
Posting Yak Master

195 Posts

Posted - 2004-06-13 : 19:14:12

Id guess that you arent taking into account the extra column being returned (counter)

try running it in query analyser, at least you would then know if the sql is working or not, then try to tie it in with the asp
Graham

gpl
Posting Yak Master

195 Posts

Posted - 2004-06-13 : 19:17:52

I might have got the syntax wrong, try this variant


Select Title, Description, Manufacturer, Price,
    (Select Count(*)
    From <table> t2
    where t2.title = t2.title and t2.price < t1.price) As Counter
from <table> t1
Where (Select Count(*)
    From <table> t2
    where t2.title = t2.title and t2.price < t1.price) = 0
Order by Title Asc

dansqlteam
Starting Member

9 Posts

Posted - 2004-06-13 : 19:31:24

Okay thanks gpl, I've not tried your last suggestion because I've got a different way working now:

SELECT Title, Min(Price) AS xPrice, Min(Description) AS xDescription, Min(Manufacturer) AS xDescription FROM Products GROUP BY Title

I really do appreciate your help though, thanks again!!

gpl
Posting Yak Master

195 Posts

Posted - 2004-06-13 : 19:39:54

Im not 100% sure you will be returning the values all from the same row this way, I think it will return for each title, the lowest price, the description that is alphabetically the lowest and the alphabetically lowest manufacturer

but I could be wrong - post your table structure and some insert statements that create representative (if not real-world) values, then we can play around with it
Graham

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources