T-SQL SELECT - Need help returning distinct values

Question

T-SQL SELECT - Need help returning distinct values

ecupirate

Grasshopper

Points: 12
More actions
July 14, 2011 at 7:54 pm

#239620

Somewhat of a newbie when it comes to T-SQL so I will try my best to explain :). I have the following columns in a table (along with sample data):
Vendor | Title | Version | Pic | InstallCount
Microsoft Office 2010 1.jpg 12
Microsoft Excel 2010 1.jpg 7
Microsoft Office 2007 1.jpg 11
Microsoft Word 2010 1.jpg 4
I want to return distinct titles and order them by installcount. I keep getting duplicate titles returned with using DISTINCT and ORDER BY. Here's my SQL statement...
SELECT DISTINCT title, pic, vendor, installcount FROM table ORDER BY installcount
It returns:
Office
Office
Excel
Word
I want:
Office
Excel
Word
I know that DISTINCT returns unique rows, hence the two "Office" values. But I'm not sure where to go from here. Please help. I can provide more info if needed.
Thanks in advance

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

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

bopeavy SSCertifiable Points: 5869 More actions · Answer 1

Somewhat of a newbie when it comes to T-SQL so I will try my best to explain :). I have the following columns in a table (along with sample data):
Vendor | Title | Version | Pic | InstallCount
Microsoft Office 2010 1.jpg 12
Microsoft Excel 2010 1.jpg 7
Microsoft Office 2007 1.jpg 11
Microsoft Word 2010 1.jpg 4
I want to return distinct titles and order them by installcount. I keep getting duplicate titles returned with using DISTINCT and ORDER BY. Here's my SQL statement...
SELECT DISTINCT title, pic, vendor, installcount FROM table ORDER BY installcount
It returns:
Office
Office
Excel
Word
I want:
Office
Excel
Word
I know that DISTINCT returns unique rows, hence the two "Office" values. But I'm not sure where to go from here. Please help. I can provide more info if needed.
Thanks in advance

Just going on what I know from what you've given if you are getting multiple titles something else is different..Cause distinct will always give you what you ask for. So you probably need something more like this:

SELECT DISTINCT title, installcount FROM table ORDER BY installcount

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

ecupirate Grasshopper Points: 12 More actions · Answer 2

I tried and that doesn't work. the table will have duplicate values in title column that will have different values in the installcount column, for example

title installcount

Office 12

Office 10

Excel 7

Word 5

I just want to return Office, Excel, Word. Using your statement or my statement it returns Office, Office, Excel, Word.

Hope the issue makes sense. If not, here's a quick description of my app and why I want to do this:

- The user is presented with a list of titles (and pics) on the main page

- When the user clicks on a title, it takes them to another page to choose a version

- On the main page a filter called "Most popular" can be applied (popularity based on installcount)

So now, when the users use the "most popular" filter, the main page lists the same title multiple times

Thanks in advance.

bopeavy SSCertifiable Points: 5869 More actions · Answer 3

SELECT DISTINCT title, installcount FROM table ORDER BY installcount
Forum Newbie
I tried and that doesn't work. the table will have duplicate values in title column that will have different values in the installcount column, for example
title installcount
Office 12
Office 10
Excel 7
Word 5
I just want to return Office, Excel, Word. Using your statement or my statement it returns Office, Office, Excel, Word.
Hope the issue makes sense. If not, here's a quick description of my app and why I want to do this:
- The user is presented with a list of titles (and pics) on the main page
- When the user clicks on a title, it takes them to another page to choose a version
- On the main page a filter called "Most popular" can be applied (popularity based on installcount)
So now, when the users use the "most popular" filter, the main page lists the same title multiple times
Thanks in advance.

If you only want to return that you will not be able to get the installcount:

SELECT DISTINCT title FROM table ORDER BY installcount

But is what your looking for more like this:

title installcount

Office 12, 10

Word 7

Excel 5

:cool:

"There are no problems! Only solutions that have yet to be discovered!"

pbarnthson SSC Veteran Points: 247 More actions · Answer 4

If you're trying to list the titles by total number of installs regardless of version, you'll need GROUP BY and SUM() statements. Give this a try:

SELECT Title

FROM Table

GROUP BY Title

ORDER BY SUM(InstallCount) DESC