How can I add the row number in the result tuples of my SQL query?
-
My query is: SELECT COUNT(*) AS count FROM moocdb.submissions GROUP BY user_id ORDER BY count DESC I would like to have as a result: row number, result of the COUNT(*) 1, 24549 2, 18277 3, 11643 4, 10736 5, 10648 .... I'm interested in knowing how to achieve that for any RDMS.
-
Answer:
For MySQL: SELECT @n := @n + 1 `Number of Submissions`, t.* FROM (SELECT @n:=0) initvars, ( SELECT COUNT(*) AS count FROM moocdb.submissions GROUP BY user_id ORDER BY count DESC ) t Related: http://dev.mysql.com/doc/refman/5.5/en/set-statement.html http://dev.mysql.com/doc/refman/5.0/en/user-variables.html http://stackoverflow.com/a/1010042/395857
Franck Dernoncourt at Quora Visit the source
Other answers
;with cte as ( SELECT COUNT(*) AS [count] FROM moocdb.submissions GROUP BY user_id ) select row_number() over (order by [count] desc) as [row number], [count] from cte order by 1
Eric LePoudre
Related Q & A:
- How can I find the minimum number of open rectangles in a grid?Best solution by Stack Overflow
- How can I convert a string number to a number in Perl?Best solution by Stack Overflow
- How can I get a Vanity number?Best solution by phonenumberguy.com
- I want to add my personal photos. how can i add it.
- Please help me how can i change my mobile number in yahoo messanger?Best solution by answers.yahoo.com
Just Added Q & A:
- How many active mobile subscribers are there in China?Best solution by Quora
- How to find the right vacation?Best solution by bookit.com
- How To Make Your Own Primer?Best solution by thekrazycouponlady.com
- How do you get the domain & range?Best solution by ChaCha
- How do you open pop up blockers?Best solution by Yahoo! Answers
For every problem there is a solution! Proved by Solucija.
-
Got an issue and looking for advice?
-
Ask Solucija to search every corner of the Web for help.
-
Get workable solutions and helpful tips in a moment.
Just ask Solucija about an issue you face and immediately get a list of ready solutions, answers and tips from other Internet users. We always provide the most suitable and complete answer to your question at the top, along with a few good alternatives below.