Questions tagged [rank]
The rank tag has no usage guidance.
69
questions
-3
votes
2
answers
51
views
Rank over discontinuous blocks of rows [closed]
I have the following table Logs:
Id
Char
1
a
2
a
3
a
4
b
5
b
6
a
7
a
8
c
I want to get something like:
Id
Char
Rank
1
a
1
2
a
2
3
a
3
4
b
1
5
b
2
6
a
1
7
a
2
8
c
1
A MS-SQL-Server ...
0
votes
0
answers
98
views
Dynamic RANKX DAX Measure to Select Top % Value and filter by it
I have customer, location, and sale tables of data which when related in Power BI give similar sample data like the example below. For simplicity, I have just included the primary keys for customer ...
- 13
0
votes
0
answers
50
views
Ranking players in sql database will return always 1 when where id_user is used
I basically have a table "race" with columns for "id_race", "id_user" and columns for user predictions "pole_position", "1st", "2nd", "...
-3
votes
1
answer
25
views
Sql ranking query
I have a requirement to make tier based on rank
Example first 5 ranks tier 1
Next 5 ranks tier 2
Next 3 ranks tier 3
Next 5 ranks tier 3
Kindly let me know if any help in the sql query for above ...
2
votes
1
answer
170
views
Is there a better SQL query to find ranking of a single row in database?
I would like to obtain the ranking of a user's points in their country. For example, 23rd highest points in the US.
After some Googling, I found that the RANK function would be the best solution. Here ...
- 123
2
votes
1
answer
141
views
Select rank of single row
CREATE TABLE Player (
id BIGINT NOT NULL PRIMARY KEY,
xp BIGINT NOT NULL
);
INSERT INTO Player (id, xp) VALUES (1, 100);
INSERT INTO Player (id, xp) VALUES (2, 150);
INSERT INTO Player (id, xp) ...
- 123
0
votes
0
answers
34
views
student scores ranking or positioning on mysqli
I have a MySQL table (named "scores") where I save student scores and I want to get each student Rank using total score column which I successfully did.
Below is my code:
SELECT studentRegNo,...
1
vote
2
answers
246
views
Getting rank of row
I am using mysql Ver 14.14 Distrib 5.7.21, for Linux (x86_64).
I am having a table referral:
CREATE TABLE `referrals` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`referred_by` bigint(20) ...
- 135
1
vote
1
answer
129
views
Atomic updates to ordering in a table for a waiting list application
I am trying to implement a gamified waiting list. This waiting list is for eventual access to an application. A user on the waiting list can perform certain actions which may move them up in the ...
- 115
1
vote
0
answers
116
views
Ranking and use of cube by in an SQL query? [closed]
I am a the MSc student who does not understand this following SQL dilemma and want to be certain from an Academic standpoint.
The entire “Foodmart” database can be downloaded from: http://didawiki.cli....
- 11
0
votes
0
answers
21
views
Field with Top Ranking Field Name
Let's imagine a table structured like this:
Bucket
Red
Blue
Green
First
1
3
4
Second
6
5
2
What I'm trying to achieve is based on the values within each bucket, I'd like to generate another set of ...
0
votes
1
answer
44
views
Normalization using DENSE_RANK and LAST_INSERT_ID
db-fiddle
I have created a table called orders (orderId, customersId) and a table called orderDetails (orderId, productId). My goal is to denormalize a table called temp which is like
scanId, ...
- 163
0
votes
1
answer
78
views
Creating a table with Rank ordering in Oracle [closed]
I am trying to create a table in Oracle as follow:
create table hops.t_distribution AS
(select
UID,
CONTACT_EMAIL,
PRODUCT_ID,
rank() over(Partition by UID order by to_number(PRODUCT_ID)) as ...
- 1
1
vote
2
answers
76
views
Find the greatest difference between each unique record with different timestamps
I have a PostgreSQL table with the following schema and data:
CREATE TABLE IF NOT EXISTS T(
id uuid PRIMARY KEY,
username varchar(15),
person varchar(10),
tweets int,
...
- 13
0
votes
1
answer
734
views
Substitute DISTINCT ON for query transformation (Postgresql to Redshift)
I need to transform my Postgresql query to Redshift and I working with the following data, here is an excerpt:
In my final output, I would like only to keep rows with the highest rank (in green). And ...
- 43
0
votes
0
answers
99
views
Using rank in postgresql
With this query in postgresql
WITH ExtractedPosition AS (
SELECT P.rider_id,
P.start_date,
P.day_index,
P.start_hour,
P.end_hour,
extract(...
1
vote
1
answer
262
views
Implementation of DENSE_RANK
I am trying to implement the DENSE_RANK function of SQL Server using only basic SQL functions. I managed to implement the RANK function but I don't know how extend it to DENSE_RANK. More specifically, ...
- 144
1
vote
1
answer
36
views
How can I group together rankings by number of users?
I have a table which includes the following:
ID evar event_date ranking
1 landing 2019-01-01 1
1 content 2019-01-02 2
1 homepage 2019-01-03 3
2 ...
0
votes
2
answers
148
views
MYSQL Calculate ranking of row that is not yet inserted
I've got a table similar to this data:
| id | username | value |
|-----------|----------|-------|
| 1 | bob | 46 |
| 483 | alice | 90 |
| 176 | sue | ...
- 103
0
votes
2
answers
3k
views
Postgres Slow ROW_NUMBER() function
I have created the following table in postgres.
test=# \d leaderboard_scores;
Table "public.leaderboard_scores"
Column | Type | ...
- 101
0
votes
1
answer
37
views
Help me to RANK correctly in Access
I have such a table:
I want to rank the people, as who has the more V1 and the less V2, V3, V4, V5 be on top.
Please advise me SQL code for MS-ACCESS.
- 1
6
votes
3
answers
1k
views
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,...
- 2,549
1
vote
1
answer
202
views
Overlaps and Gap grouping in SQL Server
I am trying to find gaps in my data and then group those. The table I am working with looks like this:
runid|year |road|start |end
01010| 9 |2 |0.000 |0.585
01100| 9 |2 |0.585 ...
- 476
1
vote
1
answer
613
views
Fetch 10 paginated ranked posts of each category
Posts table as p:
| id | cid | score | ...
--------------------
| 1 | 1 | 3 | ...
Categories Table as c:
| id |...
--------------------
| 1 |...
What i need? Reterive paginated ranked ...
- 13
0
votes
1
answer
443
views
Rank using one column. If it is the same value in 2 rows rank using another column
i have a table as table1
id| hits | time
-------+--------+------
1 | 9 | 40.89
2 | 5 | 33.21
3 | 5 | 78.31
4 | 1 | 71.93
5 | 4 | 23.10
6 | 4 | 99.99
7 | 8 ...
- 3
1
vote
2
answers
413
views
User rank based on cumulative rows
UPDATE:
Full table schema and data: https://www.db-fiddle.com/f/jT3Mfwd1BJ8GrsBxxPDC8M/1
Table structure (some fields are hidden):
+---------+---------+------------+-------------+-------+-------+
| ...
- 13
1
vote
1
answer
659
views
Get Rank based per row of a student score on a subject in sql server
l have a sample data
//
USE [master]
GO
/****** Object: Database [dbTest] Script Date: 2019/03/01 12:20:40 ******/
CREATE DATABASE [dbTest]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'...
- 27
0
votes
0
answers
191
views
How to rank mysql with groupby
Guy I am trying to ranking some data from my database, and I notice that it's going very wrong when I put the group by clause;
SET @rank=0;
SELECT @rank:=@rank+1 AS RankSemGenero
,a.nome AS ...
- 101
0
votes
0
answers
127
views
Rank users by different periods and overall
I'm trying to create a ranking system for a game which should have 2 different rankings. Below you will find the Model (or the result after querying the database) which should be presented to the ...
1
vote
0
answers
60
views
Exclusive and Inclusive ranking queries in Postgres 9.6
I'm trying to build a filtering/ranking search that would let me order and optionally filter out people based on a 1-to-many relationship with job "focuses". The tables are shown at the bottom.
A "...
- 187
2
votes
1
answer
7k
views
how to get rank number that group by a column [duplicate]
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?
- 23
5
votes
2
answers
13k
views
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 ...
- 153
0
votes
1
answer
270
views
how to flatten child tables into parent
I have inherited a db, and I need to transform an existing parent-child relationship into all parents.
Let me first explain the existing data.The tables have a structure something like:
Thing ...
- 103
0
votes
1
answer
3k
views
How to find Rank of row in MySQL
I am new to MySQL. I know that ROW_NUMBER() OVER (PARTITION) will not work in MySQL. I want to convert the code from SQL Server to MySQL:
SELECT *,
ROW_NUMBER() OVER (
Partition By X....
- 25
-2
votes
1
answer
56
views
Finding a records Rank in Database SQL Server 2016
So I need to find an individual rank for a record
I have these results
EmpTot, JOIN_NUMBER, RANKRESULT, STATE
524, 21056, 1, WI
523, 20973, 2, WI
477, 21411, 3, WI
460, 20714, 4, WI
394, 20974, 5, ...
- 1
1
vote
1
answer
381
views
Get the rank of a fruit after ordering
I have the following table, called sales
+--------+--------+
| fruit | amount |
+--------+--------+
| cherry | 124 |
| plum | 23 |
| pear | 19 |
| apple | 13 |
| banana | 4 |...
- 13
9
votes
1
answer
10k
views
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 ...
- 60.3k
5
votes
1
answer
2k
views
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 ...
- 591
3
votes
1
answer
1k
views
Using RANK function, how can I return the rank of one record vs the entire table?
I have the following info in my database:
+-----------------------+-------------+
| USERNAME TRUST | |
+-----------------------+-------------+
| DeaDTerra | 390 ...
1
vote
1
answer
794
views
Get user rank handling equal scores
Imagine there is a table with users logins and their scores:
+----+-------+-------+
| id | login | score |
+----+-------+-------+
| 1 | log1 | 4 |
+----+-------+-------+
| 2 | log2 | 4 |
+...
- 113
3
votes
2
answers
2k
views
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 ...
- 1,310
2
votes
1
answer
3k
views
Percentile rank that takes sorted argument (or same functionality) in PostgreSQL 9.3.5
How do I calculate the percentile of the nth element in a field relative to the ones previous to it in a time series for each subset (partition) of my data?
For example, this is exactly what I want ...
0
votes
1
answer
680
views
SQL Full Text Search - Result Order changing
I am using Full Search text to implement search functionality. Basically I will have to do search on all the columns with the search string. Search string will have combination of values from ...
- 1
0
votes
2
answers
1k
views
Rank rows using weighted average with multiple sub queries
So what I want to do, is to rank students based on their weighted average which will be calculated by separating the grades based on the type (whether "exam" or "not exam). The average for both will ...
- 5
0
votes
2
answers
4k
views
Rank students based on their average in MySQL
I have been researching and trying various queries to get this to work.
What I want to happen is to rank each student based on their average which will be calculated by using AVG(grade) and this will ...
- 5
3
votes
3
answers
2k
views
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) ...
- 1,327
2
votes
1
answer
15k
views
How to set rank per group with a SQL UPDATE statement?
Please first refer to the table structure:
Now you could see there is one unique constraint including manager_code, archive_year and archive_day_of_year.
I need to rank the managers for every group ...
- 59
3
votes
1
answer
792
views
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 ...
- 33
3
votes
2
answers
394
views
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, ...
- 211
0
votes
1
answer
163
views
Create a temp table that consolidates start and end dates plus generates a uniqueid for each consolidated record
I have a table called projecthistory,
it is actually a view so I could take advantage of the Rank function to get a unique record number.
From this view I need to create a temp table that has a ...
- 11