Hot answers tagged

32

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 @...


10

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 ...


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 ...


9

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' ...


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

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 ...


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

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 ...


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

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

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

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

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

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

With MySQL 8 SELECT sn, color, value FROM ( SELECT sn, color, value, DENSE_RANK() OVER (PARTITION BY color ORDER BY sn) AS r FROM table ) AS t WHERE t.r <= 2; Using MySQL < 8 You need to use their special variables.. something like this


3

Try something like WITH cte AS ( SELECT student_id, subject, score, CAST(RANK() OVER (PARTITION BY subject ORDER BY score DESC) AS CHAR(3)) + ' out of ' + CAST(COUNT(student_id) OVER (PARTITION BY subject) AS CHAR(3)) AS [rank] FROM table -- ORDER BY student, subject ) SELECT * /* subject, score, rank */ FROM cte WHERE student_id = 'R135722F'


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

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 ...


2

SELECT T1.id, T1.`name`, T1.history, T2.rank FROM test T1 LEFT JOIN ( SELECT history, (@v_id := @v_Id + 1) AS rank FROM ( SELECT DISTINCT history FROM test ORDER BY history DESC ) t, (SELECT @v_id := 0) r ) ...


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

The query you posted is actually running without syntax errors? You need an average over a CASE like this: SELECT * FROM ( SELECT student_id, avg, exam,(exam) + (avg) as total, (@rn := @rn + 1) AS rank FROM ( SELECT student_id, AVG(CASE WHEN type <> 'exam' THEN grade END) / 100 * 40 as avg, AVG(CASE WHEN ...


2

In Postgres 9.4+, you can use the hypothetical-set aggregate function percent_rank() in a LATERAL join like this: SELECT * FROM daily_data d, LATERAL ( SELECT round(percent_rank(d.daily_val) WITHIN GROUP (ORDER BY daily_val)::numeric , 6) AS pctl_calc FROM daily_data WHERE company_id = d.company_id AND trade_date < d....


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