A SQL window-function that returns the rank of each row within a result set partition.
Questions tagged [sql-rank]
20 questions
9
votes
5 answers
Determine Rank based on Multiple Columns in MySQL
I have a table which has 3 fields, I want to rank column based on user_id and game_id.
Here is SQL Fiddle :
http://sqlfiddle.com/#!9/883e9d/1
the table already I have…
Hamed M
- 329
- 2
- 10
3
votes
1 answer
Using RANK OVER PARTITION to Compare a Previous Row Result
I'm working with a dataset that contains (among other columns) a userID and startDate. The goal is to have a new column "isRehire" that compares their startDate with previous startDates.
If the difference between startDates is within 1 year,…
Rahmor
- 33
- 5
1
vote
2 answers
MySQL select multiple id at the same time with optional id with php
I would like to complicate this request by changing the scenario.
Here is the link to the original request.
Here is the link to the original request.
I have the following MySQL table called…
nusima
- 15
- 3
1
vote
1 answer
Redshift get rows with highest priority and timestamp
I have a table in redshift with records as follows
+====+======+=========+============+
| Id | Book | Action | Timestamp |
+====+======+=========+============+
| 1 | ABC | ADDED | 1111111111 |
+----+------+---------+------------+
| 1 | ABC …
rjain
- 23
- 4
1
vote
2 answers
TO find out same student is associated with how many subjects in different group
I have below Table, Need to exract those records where Same Value is associated with more than one different GroupId. If the same value is being duplicated under same GroupId then don't need to fetch that.
GroupId Value
11 AAA
11 AAA
11 …
Janny
- 25
- 7
1
vote
2 answers
MySQL error: What is wrong with my syntax for ranking?
My MySQL version is 5.6.
I have a MySQL query that I getting error and need help to understand what I am doin wrong.
Here is the query:
select images.thumbnail16x9_file,
images.square_file,
RANK() OVER (ORDER BY images.timestamp desc)…
VictorGram
- 2,521
- 7
- 48
- 82
0
votes
1 answer
MYSQL: Incremental Sorting of Duplicate Numbers
I have a table that looks like this:
id
values
1
1
2
1
3
1
4
2
5
2
6
2
I would like to have them sorted incrementally in some sort of loop that orders them in this…
meks285
- 29
- 5
0
votes
0 answers
MySQL group by for row_number
I have the compilation of all possible scenarios available to me in a SQL view. I need to get the position based on the 3 parameters: attr_1, attr_2, attr_3 considering the date as to order by.
A customer is given attr_1 can make 3 choices…
Kami Sli
- 1
0
votes
2 answers
Calculate Rank Based on Shared Column Values and Consecutive Date Ranges (same rank for records with consecutive range)
I am trying to get the rank of a table that has specific id's and a start and end date for each record, as…
DizzleBeans
- 173
- 6
0
votes
1 answer
Select first rank if RANK()+1 overflows with JOINS
I have a table called loop_msg with msg_id and content. I have a second table called loop_msg_status with channel and msg_id. This is used to post messages as round-robin in different channels, so I need to keep track of which msg_id has been posted…
dan
- 3,439
- 17
- 54
- 82
0
votes
2 answers
Creating a rank column after UNION
I'm attempting to create a SalesRank column based on the amount in column SalesAmount.
I'm attempting to do something similar to this: RANK() OVER(Order BY SalesAmount desc) as StateRank, but I am unsure on how to execute this after joining two…
kurtixl
- 419
- 4
- 17
0
votes
1 answer
Multiple ranks (with different partitions, but the same sort order) not using indexes
Similar to this question but the advice given doesn't seem to work in my case
I have a #temp table that looks something like this:
CREATE TABLE #C (
OutA int,
OutB int,
OutC int,
SortD float,
PartE nvarchar(400),
PartF…
DJL
- 2,060
- 3
- 20
- 39
0
votes
2 answers
Ranking based on product holdings
I have a table that has customer id and product id combination.
I want to identify product holding combinations that have the greatest yield.
i.e. what are the product combinations and product sub combinations or individual products that have the…
Prak_Rum
- 25
- 1
- 11
0
votes
2 answers
Window lag sqlzoo.net #7
The query shown includes a JOIN t the world table so we can access the total population of each country and calculate infection rates (in cases per 100,000).
Show the infect rate ranking for each country. Only include countries with a population of…
0
votes
3 answers
Selecting nth top row based on number of occurrences of value in 3 tables
I have three tables let's say A, B and C. Each of them has column that's named differently, let's say D1, D2 and D3. In those columns I have values between 1 and 26. How do I count occurrences of those values and sort them by that…
adammo
- 171
- 10