3

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

  • A number A which is guaranteed to be unique in the result (not in the source table); the result is ordered by A, but the values of A in the result are not necessarily continuous.
  • A key B which is repeated for multiple rows, tagging them as part of the same group. It comes from the same table as A.

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.

Protected
  • 362
  • 1
  • 7
  • 16

3 Answers3

6

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).

João Silva
  • 89,303
  • 29
  • 152
  • 158
  • You have to `ORDER BY b, a` in the internal query. – ypercubeᵀᴹ Aug 24 '12 at 12:23
  • For now I'll accept this answer, it being the earliest and highest rated. If one of the others happens to be more efficient, please let me know! – Protected Aug 24 '12 at 14:57
  • @Protected The answer is highly voted but needs a minor correction. The internal `ORDER BY b` needs to changed to `ORDER BY b,a` to give correct numbers in the `C` column. – ypercubeᵀᴹ Aug 25 '12 at 09:44
  • 1
    @Protected: If you can add an index to the table, covering `b, a`, i.e., `CREATED INDEX idx ON tbl(b, a)` you could do an index-only scan, which is highly efficient. – João Silva Aug 25 '12 at 11:41
4
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

bpgergo
  • 15,669
  • 5
  • 44
  • 68
Pradeeshnarayan
  • 1,235
  • 10
  • 21
0

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 ;
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • Check this similar question, at DBA.SE: [Sorting the Table and getting the position](http://dba.stackexchange.com/questions/18316/sorting-the-table-and-getting-the-position/18326#18326) – ypercubeᵀᴹ Aug 24 '12 at 12:44