We’re rewarding the question askers & reputations are being recalculated! Read more.

Hot answers tagged

31

SELECT id, name, score, FIND_IN_SET( score, ( SELECT GROUP_CONCAT( score ORDER BY score DESC ) FROM scores ) ) AS rank FROM scores gives this list: id name score rank 1 Ida 100 2 2 Boo 58 5 3 Lala 88 4 4 Bash 102 1 5 Assem 99 3 Getting a single person score: SELECT id, name, score, FIND_IN_SET( score, ( SELECT GROUP_CONCAT( ...


30

When multiple entries have the same score, the next rank should not be consecutive. The next rank should be incremented by number of scores that share the same rank. To display scores like that requires two rank variables rank variable to display rank variable to calculate Here is a more stable version of ranking with ties: SET @rnk=0; SET @rank=0; SET @...


16

select * from ( SELECT Subject, Name, RANK() OVER (PARTITION BY Subject ORDER BY Score DESC) as RN FROM Table ) a where a.RN <= 2


15

You could put the original query using rank() into a subquery and wrap it with a query that filters the results.


13

SELECT id, Name, 1+(SELECT count(*) from table_name a WHERE a.Score > b.Score) as RNK, Score FROM table_name b;


9

One option would be to use USER variables: SET @i=0; SELECT id, name, score, @i:=@i+1 AS rank FROM ranking ORDER BY score DESC;


9

This will happen if you have ties for the lowest frequency The formula for figuring out the percentile rank is the following (rk -1)/(rn -1) where rk equals the rank of the value and rn equals the count of the items. The below shows an example along with a calculated column showing how the PERCENT_RANK is calculated. As the highest RANK is 3 when ...


8

RANK() is a deterministic function, meaning that duplicates will be labelled with the same rank value. The outputs of your queries suggests to me that there are multiple records with the same user_id and image_id that also have the same created_at value. These records will all return with the same RANK() value. If you run your inner query, you will see ...


7

The CTE is not needed here and poses as optimization barrier. A plain subquery generally performs better: SELECT * FROM ( SELECT id ,rank() OVER w AS global_rank ,lag(slug) OVER w AS previous_slug ,lead(slug) OVER w AS next_slug FROM entries WHERE competition_id = 'bdd94eee-25a4-481f-b7b5-37aaed953c6b' ...


7

So the trick here is a property of two equally incrementing series which produce a difference that can be used to identify islands {11,12,13} - {1,2,3} = {10,10,10}. This property isn't enough to identify islands in and of itself, but it's a crucial step that we can exploit to do so. Background Stepping aside from the problem. Let's check this out.. Here ...


6

CTE: You can embed you SELECT with RANK() into a CTE and then UPDATE the CTE. WITH cte AS ( SELECT *, r = RANK() OVER(PARTITION BY archive_day, archive_year, branch_code ORDER BY open_count) FROM @data ) UPDATE c SET rank_in_department = r FROM cte c; Don't forget the ; terminator at the end of the line preceding the CTE statement. Sub Query: ...


5

You just need SELECT Location, Reference, Year, Int1, Int2, ROW_NUMBER() OVER (PARTITION BY Year, Reference ORDER BY Int2 DESC) AS [Id] FROM Value To give your desired results (online demo). In the event of tied Year,Reference,Int2 this will arbitrarily assign a sequential numbering ...


5

You could use rank() and do integer division with number of rows for each bin. declare @T table(id int, title varchar(100), price int); insert into @T(id, title, price) values (19, 'Deux fillettes, fond jaune et rouge ', 11), (17, 'Flowers in a Pitcher ', 12), (5 , 'Composition with Red, Yellow and Blue ', 12), (18, 'La ...


4

RANK() OVER (PARTITION BY xxx, yyy ORDER BY zzz) provides a number ranking for each row where xxx and yyy are identical, and orders that list by zzz The sub-query contained in your question: SELECT Sr_No FROM ( SELECT *, [Rank] = RANK() OVER (PARTITION By Email, Campaign_Name ORDER By Sr_No DESC) FROM dbo.LeadsContacts ) T WHERE T.Rank>1 ...


4

I hate giving complete solutions but here you go: START TRANSACTION; create table total_sc_tmp as (select student_id, SUM(total_score) tot_sc FROM scores GROUP BY student_id); select student_id, tot_sc, FIND_IN_SET( tot_sc , (SELECT GROUP_CONCAT( distinct tot_sc ORDER BY tot_sc DESC) AS score FROM total_sc_tmp)) as rank from ...


4

The accepted answer has a potential problem. If there are two or more identical scores, there will be gaps in the ranking. In this modified example: id name score rank 1 Ida 100 2 2 Boo 58 5 3 Lala 99 3 4 Bash 102 1 5 Assem 99 3 The score of 58 has rank 5, and there is no rank 4. If you want to make sure there are no gaps in ...


3

From Itzik Ben-Gan's Microsoft SQL Server 2012 High-Performance T-SQL Using Window Functions (Developer Reference): Logical Query Processing Order: FROM WHERE GROUP BY HAVING SELECT Evaluate Expressions Remove Duplicates ORDER BY OFFSET-FETCH/TOP Window Functions operate in steps 5.1. and 6. Obviously then, you cannot access the results of a ...


3

;WITH cteA AS(SELECT Name,GroupID, DENSE_RANK () OVER(ORDER BY Name) AS New_GroupID FROM #T) UPDATE cteA SET GroupID = New_GroupID Now , depending of your system , this could take a few seconds or more. You can split the update , to do in chunks. Something like A-G , then from G to M ... and you can add , in the DENSE_RANK something like ...


3

Like someone said before, use it in 2 queries. select t1.id, sum(pointsup - pointsdown) as points INTO #points from table1 t1 join table2 t2 on t2.Id = t1.id where t1.ActivityTime BETWEEN '2015-01-01' AND '2016-01-01' group by t1.id ; select DISTINCT p.* FROM #points p INNER JOIN joindate j ON p.id=j.id ORDER BY p.points, j.joindate ;


3

Maybe I don't get your question right. But here is code for such a ranking. You may need to adapt it to your use-case. -- Create demo data CREATE TABLE #users(user_id int identity(1,1), points int, registration datetime) INSERT INTO #users(registration) VALUES (GETDATE()), -- 1: first, because of highest points (GETDATE()), -- 2: fourth (...


3

This solution gives the DENSE_RANK in case of ties: SELECT *, IF (@score=s.Score, @rank:=@rank, @rank:=@rank+1) rank, @score:=s.Score score FROM scores s, (SELECT @score:=0, @rank:=0) r ORDER BY points DESC


3

Here's the best answer: SELECT 1 + (SELECT count( * ) FROM highscores a WHERE a.score > b.score ) AS rank FROM highscores b WHERE Name = 'Assem' ORDER BY rank LIMIT 1 ; This query will return: 3


3

You would need an index on each column in order to avoid a sort of every row and column during the ranking process. That would of course introduce significant overhead as scores are updated continuously. Probably not an option unless you have a high-end hardware configuration. The ranking processes could be offloaded onto a read-only copy maintained via ...


3

SELECT id, @seq := @seq + 1 AS seq, @rank := if(total_score = @sc, @rank, @seq) AS Rank, @sc := total_score AS Score FROM ( SELECT @sc := -1, @seq := 0, @rank := 0 ) init JOIN scores ORDER BY total_score DESC, id; To get rid of id and seq: SELECT Rank, Score FROM ( <the SELECT above> ) ORDER BY Rank, id; I'...


3

Put the window function in a derived table and move the filter outside of the derived table. That way the RANK() function will be calculated before the filter is applied instead of after. Here is one implementation: CREATE TABLE #btcProfile ( profile_name VARCHAR(20) NOT NULL, TRUST INT NOT NULL ); INSERT INTO #btcProfile VALUES ('DeaDTerra', 390), ('...


3

One possible approach is to define when there are breaks in time values using LAG() and then define the groups using SUM(): ;WITH ChangedCTE AS ( SELECT *, CASE WHEN [start] = LAG([end]) OVER (PARTITION BY [runid], [year], [road] ORDER BY [start]) THEN 0 ELSE 1 END AS Changed FROM [dbo].[runners] ) SELECT [runid]...


2

You need to use: DENSE_RANK() OVER (ORDER BY CustomerID, Value) Test at SQL-Fiddle


2

You're almost there: Add order by Score1, score2, and enclose it with an update statement: update players, (SELECT UserId, Name, Score1, score2, Rank FROM (SELECT userid, Name, Score1, Score2, @curRank := IF(@prevScore1 = Score1 && @prevScore2 = Score2, @curRank, @incRank) AS Rank, @incRank := @...


2

Here is one way to achieve equal ranking with variables: SELECT @lastrank := (subq1.ArticleCount = @lastcount) * @lastrank + (subq1.ArticleCount <> @lastcount) * (@rank := @rank + 1) AS Rank, subq1.University_Name, @lastcount := subq1.ArticleCount AS ArticleCount FROM ( SELECT Uni.University_Name, COUNT(*) AS ...


Only top voted, non community-wiki answers of a minimum length are eligible