Questions tagged [rank]
The rank tag has no usage guidance.
49
questions
0
votes
1answer
28 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.
5
votes
3answers
235 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,...
0
votes
1answer
125 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 ...
22
votes
4answers
46k views
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, ...
0
votes
1answer
44 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 ...
1
vote
1answer
60 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 ...
0
votes
1answer
32 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 ...
1
vote
2answers
67 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):
+---------+---------+------------+-------------+-------+-------+
| ...
1
vote
1answer
88 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'...
0
votes
0answers
25 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 ...
0
votes
0answers
61 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 ...
2
votes
1answer
116 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 ...
1
vote
0answers
30 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 "...
2
votes
3answers
699 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
vote
1answer
4k 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?
1
vote
2answers
7k 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 ...
0
votes
1answer
86 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 ...
30
votes
8answers
122k views
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 ...
0
votes
1answer
2k 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....
-2
votes
1answer
54 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
vote
1answer
303 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 |...
2
votes
1answer
4k 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 ...
5
votes
1answer
717 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 ...
2
votes
1answer
5k 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 ...
3
votes
2answers
782 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 ...
a_horse_with_no_name
47.4k9 gold badges95 silver badges126 bronze badges
2
votes
1answer
262 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
1answer
469 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 |
+...
2
votes
1answer
1k 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 ...
Erwin Brandstetter
105k11 gold badges224 silver badges354 bronze badges
0
votes
1answer
205 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 ...
0
votes
2answers
669 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 ...
0
votes
1answer
2k 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 ...
2
votes
1answer
8k 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 ...
3
votes
1answer
105 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 ...
3
votes
2answers
272 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, ...
3
votes
3answers
835 views
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 ...
1
vote
1answer
121 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 ...
0
votes
1answer
76 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 ...
2
votes
1answer
323 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 ...
1
vote
1answer
494 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 |
+----+---------------+-------+--...
1
vote
1answer
54 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 ...
2
votes
0answers
21 views
Sum of points and rank according to points with join three tables [duplicate]
I am creating a game where I am calculating the user points on the basis of user activities. Everything working fine I got the sum of points and on the basis of points I calculated the rank. I have ...
2
votes
1answer
430 views
How does RANK() work in this query? [closed]
I want to delete duplicate rows from a table. I have searched for the query online, and found the following sample:
DELETE
FROM dbo.LeadsContacts
WHERE Sr_No IN
(
SELECT Sr_No
FROM (
...
3
votes
2answers
764 views
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(...
0
votes
1answer
1k views
Best way to rank all the columns in a table and store those ranks in another table
I have SQL Server 2008 and a table w/ hundreds of thousands of records and about 50 columns.
I run a sql statement like:
Select
rank() over(order by col1 desc) as Col1Rank,
rank() over(order by ...
0
votes
1answer
594 views
Rank function creating duplicate partitions? (two rank 1's for the same key)
The below query is outputting two rows. Given that I am partitioning on ref_number and also have ref_number in the where clause, I believe I should only ever get a single row back.
WITH rank_cte AS (...
a_horse_with_no_name
47.4k9 gold badges95 silver badges126 bronze badges
0
votes
1answer
905 views
Sql Rank for records contain multiple combinations
I am trying to filter the records which contains multiple combinations and removing the records which contains a single combination. Database DB2
Query Used:
select *
from
(select b.Store,b....
1
vote
1answer
848 views
Selecting, ranking and applying a point score based on rank
I am writing an MVP system based on votes from users. The table will look like
vote | game | year
--------- ------ ------
player 1 | 2 | 2015
player 1 | 2 | 2015
player 2 | 2 | 2015
...
7
votes
1answer
5k views
How to optimise window queries in postgres
I have the following table with approximately 175k records:
Column | Type | Modifiers
----------------+-----------------------------+-----------------------...
0
votes
1answer
335 views
Unsure of Rank / Dense_Rank - Mulitple Columns - change rank when unique combination changes
I need to get the unique combinations of 2 fields, with a RankID for each unique combination
In this example I need to create new IDs for each Customer/Value combination.
Source table:
CustID ...
