# MySQL SELECT ranking question
**URL:** https://boards.straightdope.com/t/mysql-select-ranking-question/426381
**Category:** Factual Questions
**Created:** [November 14, 2007, 4:48pm UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381 "2007-11-14T16:48:57Z")
**Posts on this page:** 19
**Page:** 1
### Author:  [@FlyingCowOfDoom](https://boards.straightdope.com/u/FlyingCowOfDoom)
#### Post date: [November 14, 2007, 4:48pm UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/1 "2007-11-14T16:48:57Z")
I have a table that is a list of names of items sold, the quantity, and price.
```auto
Item Qty Price Total
ItemA 12 10 120
ItemB 2 20 40
ItemA 2 10 20
ItemC 4 30 120
ItemD 1 40 40
ItemB 3 20 60
```
What I am trying to do is the get the results of SELECT item, sum(qty) as tq, sum(total) as tp FROM items GROUP BY item ORDER BY tq DESC with rankings, so that the results look like this
```auto
Rank Item tq tp
1 ItemA 14 140
2 ItemB 5 100
3 ItemC 4 120
4 ItemD 1 40
```
Is this even possible with one query? I can’t figure out how to do it. TIA!
–FCOD
---
### Author:  [@Rhythmdvl](https://boards.straightdope.com/u/Rhythmdvl)
#### Post date: [November 14, 2007, 5:06pm UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/2 "2007-11-14T17:06:20Z")
Not that I can SQL my way out of a paper bag, but why are you trying to do this in one query? My first (albeit typically clumsy) instinct would be to base the query you’re looking for on a summation query one step earlier.
---
### Author:  [@FlyingCowOfDoom](https://boards.straightdope.com/u/FlyingCowOfDoom)
#### Post date: [November 14, 2007, 5:33pm UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/3 "2007-11-14T17:33:21Z")
[QUOTE=Rhythmdvl]
Not that I can SQL my way out of a paper bag, but why are you trying to do this in one query? My first (albeit typically clumsy) instinct would be to base the query you’re looking for on a summation query one step earlier.
[/QUOTE]
The actual table on which this will run is hundreds of thousands of records, and I’d like it to run as quickly as possible.
–FCOD
---
### Author:  [@Turek](https://boards.straightdope.com/u/Turek)
#### Post date: [November 14, 2007, 5:45pm UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/4 "2007-11-14T17:45:31Z")
Can you do
ORDER BY SUM(qty)?
---
### Author:  [@chrisk](https://boards.straightdope.com/u/chrisk)
#### Post date: [November 14, 2007, 5:56pm UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/5 "2007-11-14T17:56:11Z")
[QUOTE=Turek]
Can you do
ORDER BY SUM(qty)?
[/QUOTE]
Yeah, I’d go that way. Not sure if there’s any way to get the actual ranking values in there - I’d probably do that with a loop counter in whatever program is calling mysql and outputting the results. sql by its nature isn’t the right tool for every job.
---
### Author:  [@Turek](https://boards.straightdope.com/u/Turek)
#### Post date: [November 14, 2007, 6:09pm UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/6 "2007-11-14T18:09:42Z")
[QUOTE=chrisk]
Yeah, I’d go that way. Not sure if there’s any way to get the actual ranking values in there - I’d probably do that with a loop counter in whatever program is calling mysql and outputting the results. sql by its nature isn’t the right tool for every job.
[/QUOTE]
I can get the rankings in there, but it’s in MS SQL Server and it uses a temp table.
---
### Author:  [@Malacandra](https://boards.straightdope.com/u/Malacandra)
#### Post date: [November 14, 2007, 6:55pm UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/7 "2007-11-14T18:55:07Z")
Inline view? SELECT… FROM (SELECT…) AS MYVIEW, then your inner SELECT is queried just as if it were a view or table in its own right - put your GROUP BY in there - and your outer SELECT can ORDER BY all it likes. Efficiency-wise, it should run just fine bearing in mind the necessary overheads (sorting and so on) and it’s for sure DB2 will run faster than your own program code.
---
### Author:  [@ultrafilter](https://boards.straightdope.com/u/ultrafilter)
#### Post date: [November 14, 2007, 7:43pm UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/8 "2007-11-14T19:43:53Z")
Create a view V that has everything but the rank, and something like this should work:
```auto
SELECT count(SELECT Item FROM V as V2 WHERE V2.tq > V1.tq) + 1 as rank, Item, tq, tp FROM V as V1
```
So for each item in V, you’re querying V for all the items whose total quantity sold is less than the current item’s, counting them, and adding one. If you want to do this without an intermediate query, you can just replace V with the SQL used to define it everywhere it shows up.
On the other hand, if you want to do this in some imperative language, all you have to do is initialize a counter to 1 and walk over the records in order. For each record, you add a field “Rank” with the current value of the counter and add 1. That’s almost certainly going to be faster, unless the query optimizer is way smarter than I think it is.
---
### Author:  [@Arnold\_Winkelried](https://boards.straightdope.com/u/Arnold_Winkelried)
#### Post date: [November 14, 2007, 11:08pm UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/9 "2007-11-14T23:08:12Z")
Building on what Malacandra and ultrafilter have said, you can do it using an inline view, without creating a separate view:
```auto
select * from items ;
item qty price total
ItemA 12 10 120
ItemB 2 20 40
ItemA 2 10 20
ItemC 4 30 120
ItemD 1 40 40
ItemB 3 20 60
select e.the_rank, e.item, e.tq, e.tp
from
(select d.item, d.tq, d.tp,
(select 1 + count(*)
from (select a.item, sum(a.qty) as tq from items a group by a.item) b
where b.tq > d.tq
) as the_rank
from
(select c.item, sum(c.qty) as tq, sum(c.total) as tp
from items c group by c.item
) d
) e
order by e.item, e.the_rank desc ;
the_rank item tq tp
1 ItemA 14 140
2 ItemB 5 100
3 ItemC 4 120
4 ItemD 1 4
```
---
### Author:  [@Arnold\_Winkelried](https://boards.straightdope.com/u/Arnold_Winkelried)
#### Post date: [November 15, 2007, 1:10am UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/10 "2007-11-15T01:10:49Z")
sorry, had a mistake in the order by in my previous post
```auto
select * from items ;
item qty price total
ItemA 12 10 120
ItemB 2 20 40
ItemA 2 10 20
ItemC 4 30 120
ItemD 1 40 40
ItemB 3 20 60
select e.the_rank, e.item, e.tq, e.tp
from
(select d.item, d.tq, d.tp,
(select 1 + count(*)
from (select a.item, sum(a.qty) as tq from items a group by a.item) b
where b.tq > d.tq
) as the_rank
from
(select c.item, sum(c.qty) as tq, sum(c.total) as tp
from items c group by c.item
) d
) e
order by e.the_rank ;
the_rank item tq tp
1 ItemA 14 140
2 ItemB 5 100
3 ItemC 4 120
4 ItemD 1 4
```
---
### Author:  [@FlyingCowOfDoom](https://boards.straightdope.com/u/FlyingCowOfDoom)
#### Post date: [November 15, 2007, 1:21am UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/11 "2007-11-15T01:21:59Z")
Thanks everyone!
–FCOD
---
### Author:  [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)
#### Post date: [November 15, 2007, 12:46pm UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/12 "2007-11-15T12:46:11Z")
I think that any query solution which involves re-querying the entire select set for each record to generate the rank value will have disastrous O(n^2) performance on any meaningful quantity of data. The OP says he has hundreds of thousands of records. In MySQL no less.
Perhaps you can create a temp table with an additional integer autoincrement field (typically used for the PK). Then SELECT INTO the temp table with the original GROUP … BY ORDER BY … Finally, SELECT from the temp using the autoincrement as the rank values.
Even if you have to insert an intermediate step to first group, then order by sum(qty), and finally read back with ranks, that’s one O(n) operation to group plus two O(# of distinct items) operations to sort & rank which will be vastly faster.
An interesting side question for the OP is how does the number of items compare to the number of sales records? Is it 100,000 items each sold once or twice, or 50 items each sold 5,000 times?
SQL is like APL. The fact you CAN torture it into doing it all in one SELECT (or all on one line) doesn’t mean you SHOULD.
---
### Author:  [@Arnold\_Winkelried](https://boards.straightdope.com/u/Arnold_Winkelried)
#### Post date: [November 15, 2007, 7:28pm UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/13 "2007-11-15T19:28:36Z")
I agree with you, LSLGuy. The query will be horribly inefficient for large quantities of data. I’m more used to Oracle, and in Oracle you could easily write it using an ordered inline view and the rownum pseudo-column. I looked in the MySQL help I have but I didn’t see anything about a rownum pseudo-column.
Oracle example
```auto
SQL> select * from items ;
ITEM QTY PRICE TOTAL
------------------------------ --------- --------- ---------
ItemA 12 10 120
ItemB 2 20 40
ItemA 2 10 20
ItemC 4 30 120
ItemD 1 40 40
ItemB 3 20 60
6 ligne(s) sélectionnée(s).
SQL> select rownum as the_rank, b.item, b.tq, b.tp
2 from
3 (select a.item, sum(a.qty) as tq, sum(a.total) as tp
4 from items a group by a.item order by 2 desc
5 ) b ;
THE_RANK ITEM TQ TP
--------- ------------------------------ --------- ---------
1 ItemA 14 140
2 ItemB 5 100
3 ItemC 4 120
4 ItemD 1 40
SQL>
```
I can do the sub-select easily in MySQL
```auto
select * from (SELECT item
, sum(qty)
, sum(total)
FROM items group by item
order by 2 desc) a
```
but getting the ranking was hard?!?! (for a newbie like me)
I did some googling and found this solution, using the declaration of a MySQL variable (something I have to read more about
```auto
select * from items ;
item qty price total
ItemA 12 10 120
ItemB 2 20 40
ItemA 2 10 20
ItemC 4 30 120
ItemD 1 40 40
ItemB 3 20 60
select
@rownum:=@rownum+1 the_rank,
t.*
from
(select @rownum:=0) r,
(select item, sum(qty) as tq, sum(total) as tp
from items
group by item
order by 2 desc
) t ;
the_rank item tq tp
1 ItemA 14 140
2 ItemB 5 100
3 ItemC 4 120
4 ItemD 1 40
```
---
### Author:  [@Arnold\_Winkelried](https://boards.straightdope.com/u/Arnold_Winkelried)
#### Post date: [November 15, 2007, 7:35pm UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/14 "2007-11-15T19:35:56Z")
Whoa! One last thing - I found it that the MySQL variables are incremented at display time, not at row retrieval time, making them more convenient (in this case) than the Oracle rownum pseudo-column. The query doesn’t need to be written with an inline view. This will work as well:
```auto
select
@rownum:=@rownum+1 the_rank,
t.item, sum(t.qty) as tq, sum(t.total) as tp
from
(select @rownum:=0) r,
items t
group by t.item
order by 3 desc ;
the_rank item tq tp
1 ItemA 14 140
2 ItemB 5 100
3 ItemC 4 120
4 ItemD 1 40
```
---
### Author:  [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)
#### Post date: [November 17, 2007, 3:32am UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/15 "2007-11-17T03:32:48Z")
I just popped in to add the completely useless (to the OP) nugget that Microsoft SQL Server 2005 has a function that does this without needing any subqueries etc. So to all of you MySQL and Oracle users: boo-yah, suckers!
😃
---
### Author:  [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)
#### Post date: [November 17, 2007, 2:27pm UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/16 "2007-11-17T14:27:47Z")
And the name of that function would be???
---
### Author:  [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)
#### Post date: [November 20, 2007, 6:10am UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/17 "2007-11-20T06:10:17Z")
[QUOTE=LSLGuy]
And the name of that function would be???
[/QUOTE]
[RANK()](http://msdn2.microsoft.com/en-us/library/ms176102.aspx)
Yes, it even has an intuitive name. The guys in Redmond must be slipping. (Although that said, the query builder in SQL Server Management Studio says it’s invalid SQL and won’t display queries in design mode if they have that function, despite the fact that the query runs just fine. :smack: )
---
### Author:  [@K364](https://boards.straightdope.com/u/K364)
#### Post date: [November 20, 2007, 2:17pm UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/18 "2007-11-20T14:17:38Z")
[QUOTE=SCSimmons]
I just popped in to add the completely useless (to the OP) nugget that Microsoft SQL Server 2005 has a function that does this without needing any subqueries etc. So to all of you MySQL and Oracle users: boo-yah, suckers!
😃
[/QUOTE]
boo-yah to you…
Oracle has had a rich set of SQL extensions for many years, including RANK. Google “Oracle analytic functions” to have your eyes opened!
---
### Author:  [@SCSimmons](https://boards.straightdope.com/u/SCSimmons)
#### Post date: [November 20, 2007, 4:34pm UTC](https://boards.straightdope.com/t/mysql-select-ranking-question/426381/19 "2007-11-20T16:34:49Z")
[QUOTE=K364]
boo-yah to you…
Oracle has had a rich set of SQL extensions for many years, including RANK. Google “Oracle analytic functions” to have your eyes opened!
[/QUOTE]
My company seems to be stuck on Oracle 7.3, and it doesn’t seem to work there. But I guess that’s from, like, the 19th century or something, so I guess that’s not too surprising.
😉