Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

The title may be slightly vague... however, this is the basis of what I want to do: I have people in a database ranked on a statistic, for example, 'Kills'. I want to order the kills by highest to lowest, which I can already do, but this is where I run into an issue.

I would like to then have said query find a specific bit of data, a row with a specific 'Name' as defined in the query, then get 5 rows that would be above it, and 5 rows that would be below it. It would also be helpful if it does this in a timely fashion. If you need more detail, just ask!

For anyone wondering what research I've done, I've found queries that will do this, however those queries use full table scans, which would not be optimal in this case as there is 123k+ rows of data. Also, this is in mysql.

My current query that does a full table scan and doesn't work extremely well, nor effeciently, in general:

SELECT    allusers.rank, allusers.Name, allusers.Kills
FROM (
 SELECT   AgStats.Name,
        @t:=IF(score<=>@s,@t,@r) AS rank,
        @s:=AgStats.Kills AS Kills,
        @r:=@r+1
 FROM     AgStats, (SELECT @r:=0, @s:=NULL, @t:=NULL) init
 ORDER BY AgStats.Kills DESC
) allusers, (
 SELECT COUNT(*) AS rank
FROM   AgStats
WHERE  Kills > (SELECT Kills FROM AgStats WHERE Name = 'VoidWhisperer')
) user
WHERE    ABS(allusers.rank-user.rank) <= 5
ORDER BY allusers.rank

To give an example of what I'm trying to do: You have 10 rows of data, with the row kills on the end. I want to select a specific row out of those 10 based on name, then get a number of rows above it, in this case, 5 rows above/below, done via the query?

share|improve this question
1  
A full table scan can be pretty fast if the table is narrow enough. And 123k rows is not that much. –  dezso Dec 4 '12 at 21:33
    
@dezso - i can post the query I was using in the question if need be. I tried it with it, and it eventually gave up after a very long time and loaded alot more rows than it should've. –  VoidWhisperer Dec 4 '12 at 21:35
    
It can possibly help. And do I understand you correctly: you want a window on your ordered data with a given user in the middle? –  dezso Dec 4 '12 at 21:40
    
Yes. That is what I wanted, and I'll post the query I currently have, which does a full table scan and takes for ever in addition to not providing a window in the question. –  VoidWhisperer Dec 4 '12 at 21:41
    
The query I added doesn't work because it lists large numbers of users when it should only list 5. :/ –  VoidWhisperer Dec 4 '12 at 23:22

1 Answer 1

First a query without rank. You may get more than 11 rows in the result, if there people with same number of Kills as your player:

  ( SELECT a.*
    FROM AgStats AS a 
      JOIN 
      ( SELECT Kills  FROM AgStats 
        WHERE Name = 'VoidWhisperer'
          LIMIT 1 
      ) AS k
      ON a.Kills > k.Kills
    ORDER BY a.Kills ASC
      LIMIT 5
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a 
      JOIN 
      ( SELECT Kills  FROM AgStats 
        WHERE Name = 'VoidWhisperer'
          LIMIT 1 
      ) AS k
      ON a.Kills = k.Kills
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a 
      JOIN 
      ( SELECT Kills  FROM AgStats 
        WHERE Name = 'VoidWhisperer'
          LIMIT 1 
      ) AS k
      ON a.Kills < k.Kills
    ORDER BY a.Kills DESC
      LIMIT 5
  ) 
ORDER BY Kills DESC ;

This will show no more than 11 rows, even if there hundreds of players with same number of Kills, by applying an order on the two columns: Kills, Name:

  ( SELECT a.*
    FROM AgStats AS a 
      JOIN 
      ( SELECT Kills, Name  FROM AgStats 
        WHERE Name = 'VoidWhisperer'
          LIMIT 1 
      ) AS k
      ON a.Kills = k.Kills AND a.Name > k.Name
      OR a.Kills > k.Kills
    ORDER BY a.Kills ASC, a.Name ASC
      LIMIT 5
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a 
    WHERE Name = 'VoidWhisperer'
  )
  UNION ALL
  ( SELECT a.*
    FROM AgStats AS a 
      JOIN 
      ( SELECT Kills, Name  FROM AgStats 
        WHERE Name = 'VoidWhisperer'
          LIMIT 1 
      ) AS k
      ON a.Kills = k.Kills AND a.Name < k.Name
      OR a.Kills < k.Kills
    ORDER BY a.Kills DESC, a.Name DESC
      LIMIT 5
  ) 
ORDER BY Kills DESC, Name DESC ;

It will be good if you have a an index on (Kills, Name) and a (unique) index on (Name).

Tested at SQL-Fiddle

share|improve this answer
    
Alot of users have 0-10 kills.. is it possible at all to prevent a massive # of duplicates showing up, because if the user has the same # of kills as about 100 of the other 123k people, it could lead to an issue. –  VoidWhisperer Dec 5 '12 at 0:29
    
Should it prevent the duplicates now? :) –  VoidWhisperer Dec 5 '12 at 0:36
    
Well, is there a way to prevent it from giving more than is applicable if people have the same # of kills? like if 1000 people have 0 kills, i need it to not end up spitting out 1000 entries. –  VoidWhisperer Dec 5 '12 at 0:40
    
See the second query. –  ypercube Dec 5 '12 at 0:51
    
Issue with using the name to compare it: If they have similar names, it will select ones with names like the first one: eg if the name contains super.. SuperBob,SuperMario, ETC. will selectpeople with name's beginning with super. So, is it possible to make a temporary variable to sort them by that is literal rank, or something else we could use to limit it to only 11, other than name, because name doesn't work if people have similar names, which in a table of 123k rows, is bound to happen. Alot. –  VoidWhisperer Dec 5 '12 at 1:04

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.