T-SQL Query Help - 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 2008 Forums

Transact-SQL (2008)

T-SQL Query Help

Author

Topic

ecupirate1104
Starting Member

1 Post

Posted - 2011-07-14 : 21:43:32

Somewhat of a newbie when it comes to T-SQL so I'll 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

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-07-14 : 22:27:12

"Office" has two different InstallCounts (12 and 11), which one do you want?

DISTINCT works across all selected columns so for this combination of columns you are getting distinct ROWS (title, pic, vendor, installcount)

Be One with the Optimizer
TG

parody
Posting Yak Master

111 Posts

Posted - 2011-07-15 : 08:35:16

Perhaps you mean to group?

Something like

SELECT
Title
,sum(InstallCount)
FROM table
GROUP BY Title
ORDER BY sum(InstallCount)

Of course you could use min,max or whatever if preferred.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources