Help with ranking functions

Question

Post reply

Help with ranking functions

Tom257

SSChampion

Points: 11475
More actions
April 22, 2010 at 9:24 am

#138500

I want to sort on the count of a column value

e.g. if a column value occurs 6 times then those 6 rows will appear above a value that only occurs 4 times

I've tried DENSE_RANK() , but its not that simple: the sequence is still column value. Will any of the ranking functions do this, or should I write a CTE to get the counts.

Viewing 7 posts - 1 through 6 (of 6 total)

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

chrisfradenburg SSCrazy Eights Points: 9592 More actions · Answer 1

chrisfradenburg

SSCrazy Eights

Points: 9592

April 22, 2010 at 9:47 am

#1156242

Will something like this work:



select myCol, count(*) from myTable

group by myCol

order by 2 desc

Tom257 SSChampion Points: 11475 More actions · Answer 2

Yes, I had thought as much

My code is quite complex and I can't post it - for some reason the internet filters here won't let me - I'll have to group by about 20 columns, and, to avoid that messy-looking statement I was thinking of a CTE getting the totals of each value then joining back on the value and ordering by it, but then I thought I'd come across an opportunity to use the ranking functions - they seem to almost but not quite fit what I'm trying to do.

LutzM SSC Guru Points: 107049 More actions · Answer 3

Would you mind providing table DDL including some fake sample data and expected result?

Would help a lot folks like me that prefer to have something to play with / test against.

It would also avoid that "No, that's not what I'm looking for." - "Try this" - "Neither what I need" -"Try this" loops... 😉

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Tom257 SSChampion Points: 11475 More actions · Answer 4

lmu92 (4/22/2010)

Would you mind providing table DDL including some fake sample data and expected result?

Would help a lot folks like me that prefer to have something to play with / test against.

It would also avoid that "No, that's not what I'm looking for." - "Try this" - "Neither what I need" -"Try this" loops... 😉

I tried several times to post some example code, but our internet police software at work stops me posting some kinds of SQL. (Maybe they think they're stopping employees from posting injection attacks? :unsure: )

I'll look at attaching the code it in a file tomorrow - unfortunately its dynamic SQL built by a C# application - so I have to grab the built code statements from profiler.

LutzM SSC Guru Points: 107049 More actions · Answer 5

Ok, here's a sample that might get you started:

DECLARE @tbl TABLE



(

 val CHAR(1)

)

INSERT INTO @tbl

SELECT 'a' UNION ALL

SELECT 'a' UNION ALL

SELECT 'a' UNION ALL

SELECT 'a' UNION ALL

SELECT 'a' UNION ALL

SELECT 'a' UNION ALL

SELECT 'b' UNION ALL

SELECT 'b' UNION ALL

SELECT 'b' UNION ALL

SELECT 'b' UNION ALL

SELECT 'c' UNION ALL

SELECT 'd' 

SELECT  val

FROM    @tbl

ORDER BY COUNT(val) OVER(PARTITION BY val) DESC, val DESC

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Tom257 SSChampion Points: 11475 More actions · Answer 6

Great suggestion - I had a feeling ranking/over/partition syntax would come in somewhere. I've never seen it used on a COUNT before.

Attached files show my code and your code together, sorry no sample data, Both create the same output, and both have exactly the same execution plan (also attached) - But I'll go with yours as its easier to understand and can be squeezed into the C# dynamic SQL more easily than mine.

Thanks for the help 😀