CODEWITHSUNDEEP
mysqlsqlselect
Protected
Protected

Reputation: 362

(How) can I number query result groups by row/result order in a single query?

I have a query that currently returns data with the following attributes:

Example:

+--+-+-+
|id|A|B|
+--+-+-+
| 5|1|2|
|15|3|2|
|12|4|5|
|66|6|5|
| 2|7|2|
+--+-+-+

I've seen answers here which explain how to return the row number in the result. What I do need, however, is to obtain a (preferrably 1-based) order number while keeping a distinct count for each B. In the following table, C is the desired result:

+--+-+-+-+
|id|A|B|C|
+--+-+-+-+
| 5|1|2|1|
|15|3|2|2|
|12|4|5|1|
|66|6|5|2|
| 2|7|2|3|
+--+-+-+-+

This goes a little beyond my current SQL skill, so I'll be thankful for any pointers. Including pointers to existing answers!

EDIT: Both answers below work equally well in terms of results (with a dummy wrapping query used for sorting). Thank you all for the help. Which would be the most efficient query? Consider that in my specific use case, the amount of rows returned from the original query is never very large (let's say up to 50 rows, and even that is a stretch of the imagination). Also, the original query has joins used for fetching data from other relations, although they are not relevant for sorting or filtering. Finally, it is possible for all results to have the same B, or for every one of them to have a distinct B - it can go either way or anywhere inbetween.

Upvotes: 3

Views: 224

Answers (3)

João Silva
João Silva

Reputation: 91349

What you basically want is the RANK() function. However, since it's not available in MySQL, you can simulate it with:

SELECT *
FROM (
  SELECT a, b, (CASE b 
                WHEN @partition THEN @rank := @rank + 1 
                ELSE @rank := 1 AND @partition := b END) AS c
  FROM tbl, (SELECT @rank := 0, @partition := '') tmp
  ORDER BY b, a
) tmp
ORDER BY a

DEMO (SQL Fiddle).

Upvotes: 6

Pradeeshnarayan
Pradeeshnarayan

Reputation: 1235

select p.*, @i := if(@lastB != p.B, 1, @i + 1)
,@lastB := p.B as B
from table_name p, 
(select @i := 0) vt1,
(select @lastB := null) vt2
order by B;

Try this code. (Not tested)

EDIT demo with sqlfiddle http://sqlfiddle.com/#!2/412df/13/2

Upvotes: 4

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115600

This is not going to be very efficient as your query has to be calculated twice and then a group by as well:

SELECT 
    q.* ,
    COUNT(*) AS c              --- the "Rank"
FROM 
    yourQuery AS q
  JOIN
    yourQuery AS qq
      ON  qq.B = q.B
      AND qq.A <= q.A
GROUP BY
    q.A ;

Upvotes: 0

Related Questions