How to use the result of a select as offset in an SQL query

Question

I was trying to create a single SQL query to return what I need, instead of creating 2 querys, but I need to use the result of one of the querys as the offset of the other one.

My table has user answers with scores, each user may have multiple answers in that table. And I want to calculate the middle point of the table of ordered scores.

Example:

User answers:

User 1 - 5 points
User 1 - 15 points
User 2 - 8 points
User 3 - 12 points

Ranking Table:

User 1 - 20 points
User 3 - 12 points < Middle point
User 2 - 8 points

Solution:

The first query calculates the middle point:

SELECT CEILING(count(Distinct(id_user)) / 2) as position 
FROM us_user_response 
where id_round=1

Query result:

position : 2

This second query creates the ordered ranking table:

SELECT sum(points) as score 
FROM us_user_response 
where id_round=1 
GROUP BY id_user 
Order by score DESC

Now I want to create one big query that returns the score of the middle user, I just need to use the first query result as offset of the second query:

SELECT sum(points) as score 
      FROM us_user_response 
      where id_round=1
      GROUP BY id_user 
      Order by score DESC LIMIT 1
        OFFSET (SELECT CEILING(count(Distinct(id_user)) / 2) as position 
                FROM us_user_response where id_round=1)

Of course, this doesn't work, is there any way to use a result as offset?

EDIT:

The queries work nice! My question is if there is any way to use the result of a query as the offset of another. So I could accomplish this in one single query.

I need the middle score ... the only way to get that is to get the middle element of the ordered table ... — António Almeida
– António Almeida, Commented Dec 18, 2013 at 17:08
The querys work just fine! I just wanted to take the two querys and create only one. By using the first as the offset of the second query. — António Almeida
– António Almeida, Commented Dec 18, 2013 at 17:08
Why are you using id_round=214 in the main query, but id_round=1 in the position query? It think it should work correctly if they both query the same round. — Barmar
– Barmar, Commented Dec 18, 2013 at 17:12

António Almeida · Accepted Answer · 2013-12-21 19:42:37Z

1

Try something like this:

SET @line_id = 0;
SET @line_offset = (
    SELECT CEILING(count(Distinct(id_user)) / 2) as position 
    FROM us_user_response 
    WHERE id_round = 1
);

SELECT sum(points) as score,
    IF((@line_id := @line_id + 1) = @line_offset, 1, 0) AS useit 
FROM us_user_response 
WHERE id_round = 1
GROUP BY id_user 
HAVING useit = 1
ORDER BY score;

edited Dec 21, 2013 at 19:42

António Almeida

10.2k8 gold badges62 silver badges72 bronze badges

answered Dec 21, 2013 at 3:37

Gary Krohmer

1065 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to use the result of a select as offset in an SQL query

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related