Questions tagged [rank]
62 questions
38
votes
10 answers
Get the rank of a user in a score table
I have a very simple MySQL table where I save highscores. It looks like that:
Id Name Score
So far so good. The question is: How do I get what's a users rank?
For example, I have a users Name or Id and want to get his rank, where all rows…
Michael
- 585
- 1
- 6
- 10
24
votes
4 answers
Limit results to the first 2 ranking rows
In SQL Server 2008, I am using RANK() OVER (PARTITION BY Col2 ORDER BY Col3 DESC) to return data set with RANK. But I have hundreds of records for each partition, so I will get values from rank 1, 2, 3......999. But I want only up to 2 RANKs in each…
UB01
- 917
- 2
- 8
- 18
9
votes
1 answer
Solving "Gaps and Islands" with row_number() and dense_rank()?
How does one solve the islands part of gaps-and-islands with dense_rank() and row_number(). I've seen this now a few times and I'm wondering if someone could explain it,
Let's use something like this for example data (example uses…
NO WAR WITH RUSSIA
- 54,954
- 34
- 200
- 411
7
votes
1 answer
How to optimise window queries in postgres
I have the following table with approximately 175k records:
Column | Type | Modifiers
----------------+-----------------------------+-------------------------------------
id | uuid …
Jim Neath
- 173
- 1
- 6
6
votes
3 answers
A more intelligent ntile
When using the ntile() window function, the major issue is that it arbitrarily groups into roughly equal parts regardless of the actual value.
For example with the following query:
select
id,title,price,
row_number() over(order by price) as…
Manngo
- 2,183
- 6
- 23
- 44
6
votes
1 answer
How to group data and write it's group id for each row?
I have sample data:
CREATE TABLE #T (Name varchar(5), GroupId int NULL)
INSERT INTO #T (Name) VALUES
('A'),
('A'),
('A'),
('B'),
('B'),
('C'),
('D'),
('D')
Name GroupId
----- -----------
A NULL
A NULL
A NULL
B NULL
B NULL
C …
RoninDev
- 163
- 1
- 5
5
votes
1 answer
Understand why rank() over doesn't fit to don't select duplicates rows
I would like to understand why I have different results
I have a table called active_transfert where I log image transfert
user_id | image_id | created_at
--------|----------|-----------
1 |1 |2014-07-10
1 |2 …
Mio
- 557
- 7
- 19
4
votes
2 answers
How to get latest 2 records of each group
So, I have table similar to:
sn color value
1 red 4
2 red 8
3 green 5
4 red 2
5 green 4
6 green 3
Now I need the latest 2 rows for each color, eg:
2 red 8
4 red 2
5 green 4
6 green 3
How to do it, other than using separate…
lost111in
- 143
- 1
- 1
- 3
3
votes
2 answers
How do I rank the total score in order of positions
How do I rank the total_score such that the highest total score has position 1, etc. If 2 or more have the same total score, then they should have the same position
CREATE TABLE `scores` (
`id` int(11) NOT NULL auto_increment,
`student_id`…
Moses
- 41
- 1
3
votes
2 answers
Rank vs. Distinct
Just for curiosity, if I have to use rank to tag distinct values for some columns only, and ignore whatever different values occurred on other columns, why not use distinct and just remove the other columns? Any real life examples?
SELECT * FROM…
Edgar Allan Bayron
- 1,280
- 4
- 14
- 29
3
votes
3 answers
PERCENT_RANK does not distribute over 100
I'm trying to find out the position of each given record of a query in 0-100 scale. I use PERCENT_RANK ranking function this way:
select Term, Frequency, percent_rank() over (order by Frequency desc) * 100
from Words
But when I look at the results,…
Saeed Neamati
- 1,267
- 2
- 14
- 27
3
votes
1 answer
Adding an ID column based on two other columns
I have a query which returns output as follows:
[Location] [Reference] [Year] [Int1] [Int2]
England 1 2015 13 201
England 1 2015 12 57
England 1 2015 4 14
England …
EMcDonagh
- 33
- 2
3
votes
2 answers
MySql - Ranking so it only needs to be done once
By reading around, this seems to be a reasonable way of ranking people.
The table:
UserId (int primary key)
Name (varchar(100))
Score1 (int highest scores rank 1)
Score2 (int, if Score1s are equal, the person with the highest Score2 trumps all other…
Rewind
- 211
- 1
- 2
- 7
3
votes
3 answers
Sum of points and rank according to points
I am creating a game where I am calculating the user points on the basis of user activities. Everything is working fine - I got the sum of points and on the basis of points I calculated the rank. I have two columns for points calculation: pointsup…
Singh
- 151
- 5
2
votes
1 answer
how to get rank number that group by a column
I spend hours trying to solve this, but I can't.
Well, I simply have a table call invoices
I have data like this
I want to know each customer rank by their total sum
How can I archive this?
breeze
- 23
- 1
- 3