SQL Server Query for Rank (Rownumber) and Groupings

SQL Server Query for Rank (RowNumber) and Groupings

Use "Partition by" in the ranking function OVER clause

SELECT
    Rank() over (Partition by Category Order by Value, User, Category) as ranks,
    Category, User
FROM 
    Table1
Group By
    User, Category, Value 
Order by
    ranks asc

Selecting SQL TOP N rows from group, row_number, rank not working

I am assuming that you want to get the TOP N result for the sitecode partition with total_cpu_time as order. You can put the Where clause inside if you need to filter data afterwords just filter in inner query.

You can use the Row_Number() instead of the rank(),

SELECT rs.RunID,rs.SiteCode,rs.procedure_name,rs.total_cpu_time, rn
FROM (
       SELECT 
            RunID,  
            SiteCode,
            total_cpu_time, 
            procedure_name, 
            Row_Number() OVER (PARTITION BY SiteCode ORDER BY total_cpu_time DESC) AS rn
        FROM TopProcs 
        WHERE
           RunID = 1 
           AND SiteCode IN('CAS', 'P01')
    ) rs 
WHERE  
    rs.rn < 4
ORDER BY SiteCode

This will return the top 3 rows for each SiteCode.

Query to rank rows in groups

SQL

SELECT c.name AS Country,
       p.name AS Participant,
       p.points AS Points,
       (SELECT COUNT(*)
        FROM Participant p2
        JOIN Team t2 ON p2.team_id = t2.id
        WHERE t2.country_id = t.country_id
          AND (p2.points < p.points
               OR p2.points = p.points AND p2.name <= p.name)) AS country_rank
FROM Country c
JOIN Team t ON c.id = t.country_id
JOIN Participant p ON t.id = p.team_id
ORDER BY c.name, p.points, p.name;

Online Demo

SQL Fiddle demo: http://sqlfiddle.com/#!5/f48f8/14

Explanation

A simple ANSI-SQL subselect can be used to do the same job, counting the number of records for participants in the same country with a lower score or with the same score and a name that is alphabetically no higher.

Rank based on row number SQL Server 2008 R2

Use row_number() over() with some arithmetic to calculate groups of 12 ordered by date (per productid). Change the sort to ASCendng or DESCendng to suit your need.

select *
, (11 + row_number() over(partition by productid order by somedate DESC)) / 12 as rnk
from mytable
GO

myTableID | productid      | somedate            | rnk
--------: | :------------- | :------------------ | :--
        9 | 123456         | 2018-11-12 08:24:25 | 1  
        8 | 123456         | 2018-10-02 12:29:04 | 1  
        7 | 123456         | 2018-09-09 02:39:30 | 1  
        2 | 123456         | 2018-09-02 08:49:37 | 1  
        1 | 123456         | 2018-07-04 12:25:06 | 1  
        5 | 123456         | 2018-06-06 11:38:50 | 1  
       12 | 123456         | 2018-05-23 21:12:03 | 1  
       18 | 123456         | 2018-04-02 03:59:16 | 1  
        3 | 123456         | 2018-01-02 03:42:24 | 1  
       17 | 123456         | 2017-11-29 03:19:32 | 1  
       10 | 123456         | 2017-11-10 00:45:41 | 1  
       13 | 123456         | 2017-11-05 09:53:38 | 1  
       16 | 123456         | 2017-10-20 15:39:42 | 2  
        4 | 123456         | 2017-10-14 19:25:30 | 2  
       20 | 123456         | 2017-09-21 21:31:06 | 2  
        6 | 123456         | 2017-04-06 22:10:58 | 2  
       14 | 123456         | 2017-03-24 23:35:52 | 2  
       19 | 123456         | 2017-01-22 05:07:23 | 2  
       11 | 123456         | 2016-12-13 19:17:08 | 2  
       15 | 123456         | 2016-12-02 03:22:32 | 2  

dbfiddle here

Ranking Over Row_Number in SQL

Check This.
We can achive this using Row_number() ,lag() and SUM() .

        select 
        Date,
        Name,
        Row_number()over( partition by Group_Num order by ROwiD ) Row_Num,
        Group_Num
        from 
        (
            SELECT * ,  
            SUM(R) OVER(ORDER BY RowID) Group_Num
            FROM
            (
                select *,
                Case When 
                lag(name ) OVER (ORDER BY RowID ) = name
                then 0  else 1 end  as R 
                from 
                    (
                        select DATE,NAME,
                        row_number() OVER (    ORDER BY  (select 1)) AS 'RowID'
                        from #TableName
                    )A
            )B
         )C
        order by ROwiD

OutPut :

How to use RANK to Group Matched Records

This query creates the desired result.

with cte as (
    select s2.NewAdd grp, s1.*
        , rank() over(partition by Supplier_No order by s2.NewAdd) rnk
    from sample_data s1
    inner join dupe_addresses s2 on  
        (s1.cleanAddress=s2.newAdd) or (s1.cleanRemit_Address=s2.newAdd)
)
select c1.*
from cte c1
where rnk = 1
order by c1.grp

removed the Union , and combine two join Conditions by OR instead.
so a record with both conditions may be found.

The rank() is used to calculate a rank for each row within a partition of a result set.

partition by Supplier_No used to To identify duplicate records.

finally, use the where rnk = 1 to see the group of records without repetition.

SQL RANK() versus ROW_NUMBER()

ROW_NUMBER : Returns a unique number for each row starting with 1. For rows that have duplicate values,numbers are arbitarily assigned.

Rank : Assigns a unique number for each row starting with 1,except for rows that have duplicate values,in which case the same ranking is assigned and a gap appears in the sequence for each duplicate ranking.

ROW_Number with Custom Group

Guessing here, as we don't have much clarification, but perhaps this:

SELECT wid,
       id,
       COUNT(CASE id WHEN 0 THEN 1 END) OVER (ORDER BY wid ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) +1 AS [Rank]
FROM mytbl ;

---

Related Topics