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
– PM 77-1, Commented Oct 6, 2013 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
– nano_nano, Commented Oct 6, 2013 at 17:38
Trying to understand how to help you ;-) How is the absolute row number different from the row id? — Gidil
– Gidil, Commented Oct 6, 2013 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
– nano_nano, Commented Oct 7, 2013 at 10:15
@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
– nano_nano, Commented Oct 7, 2013 at 12:29

Community · Accepted Answer · 2017-05-23 11:50:04Z

1

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.

edited May 23, 2017 at 11:50

CommunityBot

11 silver badge

answered Oct 8, 2013 at 13:33

pilcrow

59.2k14 gold badges101 silver badges147 bronze badges

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

2 Comments

nano_nano Over a year ago

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...

pilcrow Over a year ago

@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.

Laurence · Accepted Answer · 2013-10-06 17:52:32Z

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 6, 2013 at 17:52

Laurence

11k1 gold badge28 silver badges36 bronze badges

Comments

PM 77-1 · Accepted Answer · 2013-10-07 18:05:49Z

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 7, 2013 at 18:05

PM 77-1

13.4k21 gold badges73 silver badges118 bronze badges

2 Comments

nano_nano Over a year ago

Thx a lot. Let me check the SQL today evening

nano_nano Over a year ago

Ok I have run it successfull. Thank you for the help

Collectives™ on Stack Overflow

sql show partition based on calculated column with mysql

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related