Geek Answers Handbook

(How) is it possible to have the number of query result groups by row / result order in one query?

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

  • The number A , which is guaranteed to be unique as a result (not in the original table); the result is ordered by A, but the values ​​of A as a result are not necessarily continuous.
  • Key B , which is repeated for several lines, marking 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 saw the answers here that explain how to return the line number in the result. However, I need to get the order number (preferably 1 based), while maintaining a separate invoice 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 is slightly different from my current SQL skill, so I will be grateful for any pointers. Including pointers to existing answers!

EDIT: Both answers below work equally well in terms of results (using a dummy wrapper query used for sorting). Thank you all for your help. What will be the most effective request? Please note that in my specific use case, the number of rows returned from the original query is never very large (even up to 50 rows, and even this is a fragment of imagination). In addition, the original query has joins used to extract data from other relationships, although they are not related to sorting or filtering. Finally, all results can have the same B or for each of them have a clear B - it can go in any direction or in any place between them.

+3
source share
3 answers

Basically you need the RANK() function. However, since it is 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 script).

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

Try this code. (Not verified)

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

+4
source

This will not be very efficient, since your request must be calculated twice, and then the group:

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

More articles:


All Articles