Questions tagged [rank]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
1 vote
2 answers
45 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) ...
user avatar
  • 135
1 vote
1 answer
46 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 ...
user avatar
  • 115
1 vote
0 answers
79 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....
user avatar
  • 11
0 votes
0 answers
20 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 ...
user avatar
0 votes
1 answer
30 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, ...
user avatar
  • 163
0 votes
1 answer
40 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 ...
user avatar
  • 1
1 vote
2 answers
64 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, ...
user avatar
0 votes
1 answer
460 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 ...
user avatar
  • 33
0 votes
0 answers
38 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(...
user avatar
1 vote
1 answer
162 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, ...
user avatar
  • 144
1 vote
1 answer
35 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 ...
user avatar
0 votes
2 answers
72 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 | ...
user avatar
  • 103
0 votes
2 answers
2k views

Postgres Slow ROW_NUMBER() function

I have created the following table in postgres. test=# \d leaderboard_scores; Table "public.leaderboard_scores" Column | Type | ...
user avatar
  • 101
0 votes
1 answer
36 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.
user avatar
  • 1
6 votes
3 answers
737 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,...
user avatar
  • 2,337
1 vote
1 answer
164 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 ...
user avatar
0 votes
1 answer
408 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 ...
user avatar
0 votes
1 answer
195 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 ...
user avatar
  • 3
1 vote
2 answers
333 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): +---------+---------+------------+-------------+-------+-------+ | ...
user avatar
  • 13
1 vote
1 answer
530 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'...
user avatar
0 votes
0 answers
141 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 ...
user avatar
0 votes
0 answers
111 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 ...
user avatar
1 vote
0 answers
54 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 "...
user avatar
  • 177
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?
user avatar
  • 23
5 votes
2 answers
12k 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 ...
user avatar
  • 153
0 votes
1 answer
180 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 ...
user avatar
  • 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....
user avatar
-2 votes
1 answer
55 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, ...
user avatar
  • 1
1 vote
1 answer
372 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 |...
user avatar
  • 13
9 votes
1 answer
9k 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 ...
user avatar
  • 55.7k
5 votes
1 answer
1k 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 ...
user avatar
  • 567
2 votes
1 answer
743 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 ...
user avatar
1 vote
1 answer
718 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 | +...
user avatar
  • 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 ...
user avatar
2 votes
1 answer
2k 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 ...
user avatar
0 votes
1 answer
563 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 ...
user avatar
0 votes
2 answers
921 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 ...
user avatar
  • 5
0 votes
2 answers
3k 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 ...
user avatar
  • 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) ...
user avatar
2 votes
1 answer
13k 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 ...
user avatar
  • 59
3 votes
1 answer
369 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 ...
user avatar
3 votes
2 answers
378 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, ...
user avatar
  • 211
0 votes
1 answer
153 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 ...
user avatar
  • 11
1 vote
1 answer
134 views

How to get overall rank based on data regardless of scope (all items, user specific, etc.)?

Table schemas: servers - id - user_id - game_id - slug ... server_votes - id - server_id - ip_address - created_at ... So, I have a two tables: servers and server_votes, and I want to get the ...
user avatar
0 votes
1 answer
86 views

Add ranking to query output

I'm new at programming with PHP/mySQL and I have got a little problem here. This is how my code looks like: SELECT COUNT(*) AS Number , Name FROM table GROUP BY Name ORDER BY Number DESC ...
user avatar
  • 1
2 votes
1 answer
419 views

Display equal ranking if count is the same

I am trying to figure out how to display ranking correctly where if multiple authors have the same article count then they get the same ranking and then the ranking resume at the correct spot. For ...
user avatar
1 vote
1 answer
858 views

mysql logical ranking based on score

I have a table like this that keeps a total score of a history exam for students. +----+---------------+-------+---------+- | id | name | history | rank | +----+---------------+-------+--...
user avatar
  • 113
1 vote
1 answer
60 views

Recording history of ranks

Once per day, this is being executed to rank everyone in the database based on points. SET @r=0; UPDATE ranks SET previousRank=rank, rank= @r:= (@r+1) ORDER BY points DESC; The field ...
user avatar
2 votes
1 answer
419 views

Row_Num Partition in WHERE clause

I've successfully added a ROW_NUMBER() function to my SELECT statement, but I get an "invalid column name" error when I try to reference the column I generated with the ROW_NUMBER() function in my ...
user avatar
6 votes
1 answer
8k views

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 ...
user avatar
  • 163