A
A
Anton B2014-11-12 14:39:58
MySQL
Anton B, 2014-11-12 14:39:58

SELECT, IN, LIMIT. How to make a request?

Good afternoon.
Table in the database

field_afield_b
oneone
one2
one3
one4
2one
22
23
24
3one
32
33
34

Request
SELECT * FROM `table` WHERE `field_a` IN (1, 2) ORDER BY `field_b` DESC
will return all rows where `field_a` is 1 or 2, sorted by `field_b`.
Question. How to make a query so that for each value of `field_a` 2 rows sorted by `field_b` are selected? That is, for the result to be
field_afield_b
one4
one3
24
23

Answer the question

In order to leave comments, you need to log in

2 answer(s)
A
Alexander, 2014-11-12
@disc

ORDER BY `field_a` ASC, `field_b` DESC

@
@mgyk, 2014-11-12
_

This option is correct, but rather slow.

SET @rank=0; 
SET @current_field=0; 
select a.field_a, a.field_b, 
@rank:=if(@current_field = a.field_a,@rank+1,0) as rank, 
@current_field:=a.field_a as current 
FROM  (select field_a, field_b from test2 where field_a IN (1,2)  
ORDER BY  field_a, field_b DESC ) as a having rank < 3;

Look, maybe it will be easier through GROUP_CONCAT(field_b ORDER BY field_b DESC) GROUP BY field_a and filter out the first N on the client side.

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question