help with select statement - 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

Transact-SQL (2000)

help with select statement

Author

Topic

clean
Starting Member

7 Posts

Posted - 2006-05-09 : 13:37:09

i need to select records and i need them to order by the count of a certain field

so it sould order my table by which id occurs more in the table for example

example table
id desc
1 text1
1 text2
1 text3
2 text1
3 text1
3 text2

so my results will look like this
results table
2 text1
3 text1
3 text2
1 text1
1 text2
1 text3

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-09 : 13:43:12

[code]
SELECT t.[id], z.[desc]
FROM YourTable z
INNER JOIN
(
SELECT [id], COUNT(*) AS Cnt
FROM YourTable
GROUP BY [id]
) t
ON z.[id] = t.[id]
ORDER BY Cnt
[/code]

Tara Kizer
aka tduggan

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-05-09 : 13:50:06

[code]select id,
desc
from table
inner join --frequencies
(select id,
count(*) as frequency
from table) frequencies
on table.id = frequencies.id
order by frequencies.frequency[/code]
You might not want to use so many reserved words in your design...

clean
Starting Member

7 Posts

Posted - 2006-05-09 : 13:55:56

beauty! thanks that worked great, one more question related to that, if i wanted to also order by desc within the order by Cnt how would i do this

thanks again!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-09 : 13:57:29

For descending ORDER BY, you just add DESC to it:


SELECT t.[id], z.[desc]
FROM YourTable z
INNER JOIN
(
	SELECT [id], COUNT(*) AS Cnt
	FROM YourTable
	GROUP BY [id]
) t
ON z.[id] = t.[id]
ORDER BY Cnt DESC

Ascending is the default, which is why you don't have to say:

ORDER BY Cnt ASC

So...ORDER BY Cnt ASC == ORDER BY Cnt

Tara Kizer
aka tduggan

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-05-09 : 15:23:01

I think they meant the column (desc) which I'm guessing is really DESCRIPTION, but shortened to DESC.

I'm laughing to myself because I was thinking as I was reading that Tara must have missed the ", ". And then I wondered why you were explaining in such details. Then I realized what you thought thought that they meant. Clean if you meant you want it sorted by the Count and then by the DESC field if duplicates on count, then you just need to add a simple comma between Cnt and Desc in the order by clause, or perhaps identify the table as well "z" in case you have DESC in both tables:

SELECT t.[id], z.[desc]
FROM YourTable z
INNER JOIN
(
SELECT [id], COUNT(*) AS Cnt
FROM YourTable
GROUP BY [id]
) t
ON z.[id] = t.[id]
ORDER BY Cnt, z.[DESC]

Hope it helps,
Dalton

Blessings aren't so much a matter of "if they come" but "are you noticing them."

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-05-09 : 15:35:57

And that's one of the many reasons why not to use reserved words as your object names!

Tara Kizer
aka tduggan

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2006-05-09 : 16:33:01

Sorting by description? Not only an unusual business requirement, but about the slowest sort you could do as well.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources