CODEWITHSUNDEEP
phpmysqlsqloffset
António Almeida
António Almeida

Reputation: 10117

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

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:

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

Ranking Table:

  1. User 1 - 20 points
  2. User 3 - 12 points < Middle point
  3. 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.

Upvotes: 4

Views: 510

Answers (1)

Gary Krohmer
Gary Krohmer

Reputation: 106

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;

Upvotes: 1

Related Questions