Mysql: "SET @Variable = 0" in one query [Java]

Question

I have two sql queries. But in Java I can't set variables.

I tried to summarize it to one query. But that not works, because the sql syntax is wrong.

SET @rn = 0;
SELECT * 
FROM (SELECT t.id, @rn := @rn + 1 AS rank 
      FROM stats t 
      ORDER BY t.points DESC) t2 
WHERE t2.id = ?;

Are you sending the statements in one batch or splitting the individual statements? If the latter, does the Java library you are using allow sending multiple statements in one query? What exactly makes you think that Java doesn't allow functions or that the SQL syntax is wrong? Does documentation say so? Is it because you're getting an error message? — Álvaro González
– Álvaro González, Commented Jan 24, 2019 at 15:05

Juan Carlos Oropeza · Accepted Answer · 2019-01-24 14:57:38Z

0

If only the SET part is the problem you can do

SELECT t.id, @rn := @rn + 1 AS rank 
FROM stats t 
CROSS JOIN ( SELECT @rn := 0 ) as parameters
ORDER BY t.points DESC

answered Jan 24, 2019 at 14:57

48.4k14 gold badges87 silver badges128 bronze badges

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

Comments

Daniel E. · Accepted Answer · 2019-01-24 15:05:27Z

0

You need to do a join like this :

SELECT stats.id, @rn := @rn+1 AS rank
FROM stats, (SELECT @rn:=0) a
WHERE stats.id = ?

If you have mysql 8.0 you can use: ROW_NUMBER() or RANK() :

SELECT
   id,
   ROW_NUMBER() OVER w AS 'row_number',
   RANK()       OVER w AS 'rank',
FROM stats
WHERE stats.id = ?
WINDOW w AS (ORDER BY points);

answered Jan 24, 2019 at 15:05

Daniel E.

2,4811 gold badge16 silver badges25 bronze badges