sql show partition based on calculated column with mysql

Question

Let's pretend we have this relation:

╔═══════════════════╗
║ i++ name  score   ║
╠═══════════════════╣
║ 1   Will  123     ║
║ 2   Joe   100     ║
║ 3   Bill  99      ║
║ 4   Max   89      ║
║ 5   Jan   43      ║
║ 6   Susi  42      ║
║ 7   Chris 11      ║
║ 8   Noa   9       ║
║ 9   Sisi  4       ║
╚═══════════════════╝

Now I need a subset based on the data I am searching for. For instance I'm searching for the fith place. In my result I need more than the record of Jan, I need the two records before Jan and the two records behind Jan too. So I have the following resultset:

╔═══════════════════╗
║ id++ name score   ║
╠═══════════════════╣
║ 3   Bill  99      ║
║ 4   Max   89      ║
║ 5   Jan   43      ║
║ 6   Susi  42      ║
║ 7   Chris 11      ║
╚═══════════════════╝

That is the sql I got:

select @a:= id from quiz.score where username = 'Jan'; 
set @i=0;
SELECT @i:=@i+1 as Platz, s.* 
FROM quiz.score s where id BETWEEN @a-5 AND @a+5 
order by points desc;

The problem here is that @a is the id of the record. Is there a way to use the calculated value @i:=@i+1?

Thx a lot for your help.

Your `SELECT @i:=@i+1 ...` is perfectly legal in `MySQL`. Does it answer your question? If not, please clarify your problem. — PM 77-1, Oct 06 '13 at 17:34
In @a I need the absolut row number of the record Jan. Not the id. I dont know how to handle this. — nano_nano, Oct 06 '13 at 17:38
Trying to understand how to help you ;-) How is the absolute row number different from the row id? — Gidil, Oct 06 '13 at 20:50
@gidil youre another answer was correct For the field id but know i need the same For @i:=@i+1. Because id isnt the correct ordering for my purpose — nano_nano, Oct 07 '13 at 10:15
Sorry, I want to try and help you but I can't figure out what you want (and why) — Gidil, Oct 07 '13 at 11:24
@gidil: Imagine you have a simple highscore with username and points. the id is generated by the db. But it must not be a sequential number(for instance some records are removed). With the counter @i:=@i+1 and the order by I can easily create the highscore list. Now imagine I have 100thousands of records and I want to show only the record of a specific user and additional the 5 records before and the 5 records behind the user record. How I can handle this when my counter is generated by the sql? — nano_nano, Oct 07 '13 at 12:29
So, you do have ID that was set-up as `auto-increment` but then some records were deleted, so it's no longer sequential, but still `unique`, right? If this is the case, please see my [answer to a different problem](http://stackoverflow.com/questions/18947153/sql-for-generating-previous-and-next-links-in-mysql/18948196#18948196). Would this approach possibly apply? — PM 77-1, Oct 07 '13 at 16:01
Never mind *that* solution wouldn't work for you. I have, however, posted a working one. — PM 77-1, Oct 07 '13 at 18:06

score 1 · Answer 1 · answered Oct 06 '13 at 17:52

1

It's not entirely clear what you want, but a creative use of limit might be able to help:

Set @i = 0;

Select
  (@i := @i + 1) + 2 place,
  s.*
From
  quiz_score s
Order By
  quality Desc
Limit 
  2, 5;

Example Fiddle

answered Oct 06 '13 at 17:52

Laurence

10,896
1
25
34

score 1 · Answer 2 · answered Oct 07 '13 at 18:05

1

I have a working solution for you. The main point was to increment variable in WHERE clause.

The below code displays 7 rows: 3 below 'Jan', 'Jan' itself, and 3 after.

SET @i=0;

SELECT @n:=id, @s:=points FROM Score c WHERE c.username='Jan' ORDER BY points DESC;
SELECT (@i:=@i+1) FROM Score c WHERE c.id <> @n AND c.points <= @s;
SELECT (@i:=@i+1) FROM Dual;

SET @k=0;

SELECT s.* FROM Score s WHERE (@k:=@k+1) >= @i-3 AND @k<= @i+3  ORDER BY points DESC;

SQL Fiddle

Let me know if any clarifications are required.

answered Oct 07 '13 at 18:05

PM 77-1

12,933
21
68
111

Thx a lot. Let me check the SQL today evening – nano_nano Oct 08 '13 at 05:59
Ok I have run it successfull. Thank you for the help – nano_nano Oct 08 '13 at 06:08

score 1 · Accepted Answer · edited May 23 '17 at 11:50

If you do not need the rank in your output (and it appears from your comments and favored answers that you do not), you can simply combine the quiz scores nearest to Jan's score:

Query (SQL Fiddle here):

-- XXX this assumes `scores`.`username` is UNIQUE !
SELECT * FROM (
    -- Get those who scored worse (or tied)
    (    SELECT s.*
           FROM scores s
     CROSS JOIN (SELECT score FROM scores WHERE username = 'Jan') ref
          WHERE s.score <= ref.score AND username <> 'Jan'
       ORDER BY s.score DESC
          LIMIT 2)
    UNION
    -- Get our reference point record
    (SELECT s.* FROM scores s WHERE username = 'Jan')
    UNION
    -- Get those who scored better
    (    SELECT s.*
           FROM scores s
     CROSS JOIN (SELECT score FROM scores WHERE username = 'Jan') ref
          WHERE s.score > ref.score AND username <> 'Jan'
       ORDER BY s.score ASC
          LIMIT 2)
) slice
ORDER BY score ASC;

(Note that I LIMITed the results to two records before Jan and two after Jan because your sample data set was so small.)

Those parentheses on the constituent queries above are needed to allow LIMIT and UNION to work together. The outermost query then lets us ORDER the results of a UNION.

that sql works very well. is there a possibility to get a counter column in the result? Problem: I need a overall counter. not from 1 to n. I need a counter based on all existing rows but for the result... — nano_nano, Oct 10 '13 at 18:23
@StefanBeike, hmm. (MySQL's lack of ROW_NUMBER, etc. is difficult to work around.) I suppose I would JOIN the big query in my answer with your `@i:=@i+1` ranking query on some unique key to pull in the users' rank. — pilcrow, Oct 10 '13 at 18:33

sql show partition based on calculated column with mysql

3 Answers3

Linked