New to LEFT JOIN need a little help.

[unlishema.wolf]

Okay so what I have is a problem with the SELECT query. Pretty sure this is the correct spot seeing as it is with the mysql query.

 

I need to select a specific username's row get the rank of the user and get the users for one rank higher and one rank lower. So here is the code I have.

 

Query for grabbing user with rank:

SELECT z.*, x.rank
FROM `hiscore` z
INNER JOIN (SELECT a.username, a.score, @num := @num + 1 AS rank from `hiscore` a, (SELECT @num := 0) d
order by a.score DESC) x
ON z.username = x.username
WHERE z.username = 'unlishema' 
LIMIT 1

 

Query for table:

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";

CREATE TABLE IF NOT EXISTS `hiscore` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `score` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ;

INSERT INTO `hiscore` (`id`, `score`, `username`) VALUES
(1, 456, 't1'),
(2, 548, 't2'),
(3, 123, 't3'),
(4, 234, 't4'),
(5, 923, 't5'),
(6, 234, 't6');

The Query I developed to try to do this: (does not work as I don't know half of what I am doing)

SELECT * FROM `hiscore` AS z
	LEFT JOIN (select a.*, @num := @num + 1 AS rank from `hiscore` a, (SELECT @num := 0) d order by a.score DESC) w ON z.username = w.username
	LEFT JOIN (select b.*, @num := @num + 1 AS rank from `hiscore` WHERE b.rank=a.rank-1 b, (SELECT @num := 0) e order by a.score DESC) x ON z.username = x.username
	LEFT JOIN (select c.*, @num := @num + 1 AS rank from `hiscore` WHERE c.rank=a.rank+1 c, (SELECT @num := 0) f order by a.score DESC) y ON z.username = y.username
	WHERE z.username = 't2' 
	LIMIT 3

so for an example lets pull the folling usernames with ranks in correct order.

t2 with score of 548 (inital user we are pulling)

t1 with score of 456 (below user we are pulling)

t5 with score of 923 (above user we are pulling)

 

Correct order would be:

t1, t2, t5

 

Also I wanted to note that scores can be the same. It don't really matter the order of same scores if the initial score does not have any duplicate scores, otherwise we would not want the same scores to be pulled even though they are the same. (greater than score > initial score) and (less than score < initial score) NOT (greater than score >= initial score) and (less than score <= initial score)

 

Sorry for the previous paragraph I can't do paragraph formation very well.

 

I can provide additional information as needed. I can't think of anything else I would need to post at this moment.

 

Thanks in advance.

[vinny42]

One problem is probably that you are reusing the @num which is probably global for the entire query, not just the three subqueries.

 

 

Your rank is based on the order in which the records appear when they are  sorted by score, so the previous lower in rank and the next higher in rank ar just the records that come before and after the current record when sorting by score.

So, you could just use LIMIT and OFFSET.

 

If the target rank is 54 then that's the 54th record in the list. The previous record is the 53rd and the next is the 55th.

So if you merge two queries using UNION ALL, and eeach query uses OFFSET to skip the first 53 and 54 records, then you have what you need.

 

SELECT * FROM tabel ORDER BY score DESC LIMIT 1 OFFSET 53

UNION ALL

SELECT * FROM tabel ORDER BY score DESC LIMIT 1 OFFSET 54

[Barand]

People with the same score should have equal rank EG

id  score   rank
--  -----   ----
7    10      1
1     9      2
5     9      2
6     8      4
4     8      4
2     8      4
3     5      7

This, however, gives rise to another problem.  Depending on how many share the same rank there may or may not be a rank of rank+1 or rank-1.

 

Take id 4 with 8 points and rank of 4. In this case the score above has rank-2 and score below has rank+3

 

This query will find ranks as opposed to row numbers

SELECT a.id, a.score as scoreA, a.rank as rankA
FROM
    (
        SELECT id, 
        @rowa := @rowa+1 as row,
        @ranka := IF(score=@prevscorea, @ranka, @rowa) as rank,
        @prevscorea := score as score
        FROM scores
            JOIN (SELECT @rowa:=0, @ranka:=0,@prevscorea:=0) as init
        ORDER BY score DESC
    ) as a

[vinny42]

FYI: this is one area where PostgreSQL shines because it supports windowing.

 

Getting a rank literally this simple:

 

SELECT player, score, rank() OVER (ORDER BY score DESC) FROM table;
 

See: http://www.postgresql.org/docs/9.1/static/tutorial-window.html

[Barand]

vinny42, Are you just on commission from PostgreSQL or a full-time salaried evangelist?

 

Most of us who are using MySQL don't give a damn how it's done in PostgreSQL, SQL Server or any other RDBMS.

Edited September 15, 2013 by Barand

[vinny42]

 

Most of us who are using MySQL don't give a damn how it's done in PostgreSQL, SQL Server or any other RDBMS.

 

I'm sorry, I get carried away when I see a solution for MySQL that can be done much simpler in just about every other database, I just can't stop myself from pointing it out. :-)

I'll try not to do that anymore.

 

But why did you put  "we MySQL users dont give a damn about other databases" and not "how PostgreSQL does it is not relevant because the OP is using MySQL"?

 

I refuse to believe that you are not even slightly curious about windowing functions when look at the difference between your query and mine... seriously... come on, admit it :-)

[Barand]

 much simpler in just about every other database,

Have you used  MS SQL Server?

[vinny42]

What's your point?

[unlishema.wolf]

One problem is probably that you are reusing the @num which is probably global for the entire query, not just the three subqueries.

 

 

Your rank is based on the order in which the records appear when they are  sorted by score, so the previous lower in rank and the next higher in rank ar just the records that come before and after the current record when sorting by score.

So, you could just use LIMIT and OFFSET.

 

If the target rank is 54 then that's the 54th record in the list. The previous record is the 53rd and the next is the 55th.

So if you merge two queries using UNION ALL, and eeach query uses OFFSET to skip the first 53 and 54 records, then you have what you need.

 

SELECT * FROM tabel ORDER BY score DESC LIMIT 1 OFFSET 53

UNION ALL

SELECT * FROM tabel ORDER BY score DESC LIMIT 1 OFFSET 54

I believe I see what you mean with @num. I might be able to give this a try. I need to finish this project but luckily it has not been an extreme concern. I will be looking into this more. I will reply if this helped at all.

 

 

 

People with the same score should have equal rank EG

id score rank

-- ----- ----

7 10 1

1 9 2

5 9 2

6 8 4

4 8 4

2 8 4

3 5 7

This, however, gives rise to another problem.  Depending on how many share the same rank there may or may not be a rank of rank+1 or rank-1.

 

Take id 4 with 8 points and rank of 4. In this case the score above has rank-2 and score below has rank+3

 

This query will find ranks as opposed to row numbers

SELECT a.id, a.score as scoreA, a.rank as rankA

FROM

(

SELECT id,

@rowa := @rowa+1 as row,

@ranka := IF(score=@prevscorea, @ranka, @rowa) as rank,

@prevscorea := score as score

FROM scores

JOIN (SELECT @rowa:=0, @ranka:=0,@prevscorea:=0) as init

ORDER BY score DESC

) as a

 

I appreciate the help, however what you don't seem to realize is the database does not contain the rank of the user. I am determining the rank during the query based on the score. This would work great if the rank was stored in the database. The database needs to be flexible due to the nature of live stream updating. Therefore I need to do everything in 1 query to make less strain on the server due to high bandwidth traffic.

[Barand]

And you don't seem to realize that the table I am using does not contain the rank either. That is why am using the subquery to calculate @rank dynamically from the scores in a single query. The subquery effectively creates a table that DOES contain rank.