Differnece Between RANK, DENSE_RANK and ROW_NUMBER

 /*Rank , RowNumber, Danse rank*/

Rank Function - It is used to get the rank of records on the basis of any column. It skips the records if matched the same.

DENSE_RANK Function - It is used to get the rank of records on the basis of any column. It doesn't skips the records if matched the same.

ROW_NUMBER Function - It is used to get the order of records on the basis of any column. It reset itself once the shorted column values gets changed.

CREATE TABLE Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number
(
id INT,
name VARCHAR(50) NOT NULL,
company VARCHAR(50) NOT NULL,
power INT NOT NULL
)



INSERT INTO Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number
VALUES
(1, 'Corrolla', 'Toyota', 1800),
(2, 'City', 'Honda', 1500),
(3, 'C200', 'Mercedez', 2000),
(4, 'Vitz', 'Toyota', 1300),
(5, 'Baleno', 'Suzuki', 1500),
(6, 'C500', 'Mercedez', 5000),
(7, '800', 'BMW', 8000),
(8, 'Mustang', 'Ford', 5000),
(9, '208', 'Peugeot', 5400),
(10, 'Prius', 'Toyota', 3200),
(11, 'Atlas', 'Volkswagen', 5000),
(12, '110', 'Bugatti', 8000),
(13, 'Landcruiser', 'Toyota', 3000),
(14, 'Civic', 'Honda', 1800),
(15, 'Accord', 'Honda', 2000)

RANK Function
The RANK function is used to retrieve ranked rows based on the condition of the ORDER BY clause.

SELECT name,company, power,
RANK() OVER(ORDER BY power DESC) AS PowerRank
FROM Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number

Output
name        company        power    PowerRank
800            BMW             8000    1
110            Bugatti            8000    1
208            Peugeot          5400    3
Atlas        Volkswagen     5000    4
Mustang        Ford           5000    4
C500        Mercedez        5000    4
Prius        Toyota              3200    7
Landcruiser    Toyota       3000    8
Accord        Honda          2000    9
C200        Mercedez        2000    9
Corrolla    Toyota            1800    11
Civic        Honda             1800    11
City        Honda               1500    13
Baleno        Suzuki          1500    13
Vitz        Toyota               1300    15




SELECT name,company, power,
RANK() OVER(PARTITION BY company ORDER BY power DESC) AS PowerRank
FROM Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number

name                                               company                                            power       PowerRank
-------------------------------------------------- -------------------------------------------------- ----------- --------------------
800                                                BMW                                                 8000        1
110                                                Bugatti                                               8000        1
Mustang                                            Ford                                               5000        1
Accord                                             Honda                                              2000        1
Civic                                              Honda                                                1800        2
City                                               Honda                                                 1500        3
C500                                               Mercedez                                          5000        1
C200                                               Mercedez                                          2000        2
208                                                Peugeot                                              5400        1
Baleno                                             Suzuki                                              1500        1
Prius                                              Toyota                                                3200        1
Landcruiser                                        Toyota                                           3000        2
Corrolla                                           Toyota                                              1800        3
Vitz                                               Toyota                                                 1300        4
Atlas                                              Volkswagen                                        5000        1




DENSE_RANK Function

The DENSE_RANK function is similar to RANK function however the DENSE_RANK function does not skip any ranks if there is a tie between the ranks of the preceding records. Take a look at the following script.

SELECT name,company, power,
dense_RANK() OVER(ORDER BY power DESC) AS PowerRank
FROM Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number


name                                               company                                            power       PowerRank
-------------------------------------------------- -------------------------------------------------- ----------- --------------------
800                                                BMW                                                8000        1
110                                                Bugatti                                            8000        1
208                                                Peugeot                                            5400        2
Atlas                                              Volkswagen                                         5000        3
Mustang                                            Ford                                               5000        3
C500                                               Mercedez                                           5000        3
Prius                                              Toyota                                             3200        4
Landcruiser                                        Toyota                                             3000        5
Accord                                             Honda                                              2000        6
C200                                               Mercedez                                           2000        6
Corrolla                                           Toyota                                             1800        7
Civic                                              Honda                                              1800        7
City                                               Honda                                              1500        8
Baleno                                             Suzuki                                             1500        8
Vitz                                               Toyota                                             1300        9


SELECT name,company, power,
DENSE_RANK() OVER(PARTITION BY company ORDER BY power DESC) AS DensePowerRank
FROM Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number

name                                               company                                            power       DensePowerRank
-------------------------------------------------- -------------------------------------------------- ----------- --------------------
800                                                BMW                                                8000        1
110                                                Bugatti                                            8000        1
Mustang                                            Ford                                               5000        1
Accord                                             Honda                                              2000        1
Civic                                              Honda                                              1800        2
City                                               Honda                                              1500        3
C500                                               Mercedez                                           5000        1
C200                                               Mercedez                                           2000        2
208                                                Peugeot                                            5400        1
Baleno                                             Suzuki                                             1500        1
Prius                                              Toyota                                             3200        1
Landcruiser                                        Toyota                                             3000        2
Corrolla                                           Toyota                                             1800        3
Vitz                                               Toyota                                             1300        4
Atlas                                              Volkswagen                                         5000        1




ROW_NUMBER Function


Unlike the RANK and DENSE_RANK functions, the ROW_NUMBER function simply returns the row number of the sorted records starting with 1

SELECT name,company, power,
ROW_NUMBER() OVER(ORDER BY power DESC) AS RowRank
FROM Cars

name                                               company                                            power       RowRank
-------------------------------------------------- -------------------------------------------------- ----------- --------------------
800                                                BMW                                                8000        1
110                                                Bugatti                                            8000        2
208                                                Peugeot                                            5400        3
Atlas                                              Volkswagen                                         5000        4
Mustang                                            Ford                                               5000        5
C500                                               Mercedez                                           5000        6
Prius                                              Toyota                                             3200        7
Landcruiser                                        Toyota                                             3000        8
Accord                                             Honda                                              2000        9
C200                                               Mercedez                                           2000        10
Corrolla                                           Toyota                                             1800        11
Civic                                              Honda                                              1800        12
City                                               Honda                                              1500        13
Baleno                                             Suzuki                                             1500        14
Vitz                                               Toyota                                             1300        15

SELECT name, company, power,
ROW_NUMBER() OVER(PARTITION BY company ORDER BY power DESC) AS RowRank
FROM Dizsweb_Cars_SQL_Server_Rank_dense_Rank_row_number


name                                               company                                            power       RowRank
-------------------------------------------------- -------------------------------------------------- ----------- --------------------
800                                                BMW                                                8000        1
110                                                Bugatti                                            8000        1
Mustang                                            Ford                                               5000        1
Accord                                             Honda                                              2000        1
Civic                                              Honda                                              1800        2
City                                               Honda                                              1500        3
C500                                               Mercedez                                           5000        1
C200                                               Mercedez                                           2000        2
208                                                Peugeot                                            5400        1
Baleno                                             Suzuki                                             1500        1
Prius                                              Toyota                                             3200        1
Landcruiser                                        Toyota                                             3000        2
Corrolla                                           Toyota                                             1800        3
Vitz                                               Toyota                                             1300        4
Atlas                                              Volkswagen                                         5000        1










Similarities between RANK, DENSE_RANK, and ROW_NUMBER Functions
The RANK, DENSE_RANK and ROW_NUMBER Functions have the following similarities:
1- All of them require an order by clause.
2- All of them return an increasing integer with a base value of 1.
3- When combined with a PARTITION BY clause, all of these functions reset the returned integer value to 1 as we have seen.
4- If there are no duplicated values in the column used by the ORDER BY clause, these functions return the same output.

Azure SQL Server Removinng the duplicated value without rank function

 

Select SQLWorkorderType,sum(sqlworkordertypeid)

 from(

select distinct SQLType as [SQLWorkorderType],0 as sqlworkordertypeid 

from SQLWorkOrderType WOT
Union
select distinct SqlType as [SqlWorkorderType],sqlworkordertypeid from SQLWorkOrderType WOT
) as B GROUP by SqlWorkorderType

Azure SQL Server Date and Time DataType Conversions and Functions

 This document will explain the types of Date and Time  Data Type in Sql Server and related funtions.

In SQL Server Their are six Date and Time Data Types.

1- Time

2- Date

3-SmallDateTime

4-DateTime

5-DateTime2

6-DateTimeOffSet

Time 

The fractional second scale specifies the number of digits for the fractional part of the seconds. The fractional second scale ranges from 0 to 7. By default, the fractional second scale is 7 if you don’t explicitly specify it.

Format-        hh:mm:ss[.nnnnnnn]

Range-        nano seconds upto 100

Query- To create a table  with Time Column

CREATE TABLE dizsweb(
    dizswebid int

    Name varchar(50)    

    start_at TIME(0),

);

 

 The following INSERT statement adds a row to the table

INSERT INTO dizsweb (
    dizswebid ,
    Name ,
    start_at 
)
VALUES
    (   '1'
        'John Doe',
        '09:30:00'
    );

 

 

Date
To store the date data in the database, you use the SQL Server DATE data type

Format-        YYYY-MM-DD

Range-        1 Day

Query to Create a Table with Date Data Type

Create Table Dizsweb (DizswebID int, Name varchar(50), DOB Date)

Insert the data into table

Insert into Dizsweb values( 1,'SQL Azure','1994-07-28')

 

 

CREATE TABLE dizsweb (
    product_id INT NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL,
    amount DEC (10, 2) NOT NULL,
    PRIMARY KEY (
        product_id,
        valid_from,
        valid_to
    ),
    FOREIGN KEY (product_id) 
    REFERENCES production.products (product_id)
);

 

INSERT INTO dizsweb (
    product_id,
    valid_from,
    valid_to,
    amount
)
VALUES
    (
        1,
        '2019-01-01',
        '2019-12-31',
        400
    );

 

SmallDateTime

Format-        YYYY-MM-DD hh:mm:ss

Range-        1 Minute

CREATE TABLE [dbo].[delivers](

  [productid] [tinyint] NOT NULL,

  [date] [nvarchar](100) NULL,

CONSTRAINT [PK_delivers] PRIMARY KEY CLUSTERED

(

  [productid] ASC

)WITH (PAD_INDEX = OFF, 

 STATISTICS_NORECOMPUTE = OFF,

  IGNORE_DUP_KEY = OFF,  

ALLOW_ROW_LOCKS = ON, 

 ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

INSERT [dbo].[delivers] ([productid], [date]) VALUES (1, N'02-03-2005')

INSERT [dbo].[delivers] ([productid], [date]) VALUES (2, N'03-05-2006')

INSERT [dbo].[delivers] ([productid], [date]) VALUES (3, N'04-05-2011')

 

DateTime

Format-        YYYY-MM-DD hh:mm:ss[.nnn]

Range-        0.33 Seconds

 

CREATE TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25),
Birth_Date datetime);

 

CREATE TABLE Customer
(First_Name char(50),
Last_Name char(50),
Address char(50),
City char(50),
Country char(25) default 'United States',
Birth_Date datetime); 

DateTime2

Format-        YYYY-MM-DD hh:mm:ss[.nnnnnnn]

Range-        100 nano Seconds

    DECLARE @nowDateTime 

    DATETIME = GETDATE(),

    DateTime2 DATETIME2(3)= SYSDATETIME() 

    SELECT DATALENGTH(DateTime) 'DateTime Storage Size', 

    DATALENGTH(@nowDateTime2) 'DateTime2(3) Storage Size'

DateTimeOffSet

Format-        YYYY-MM-DD hh:mm:ss[.nnnnnnn]

Range-        100 nano seconds and as par the time Zone

 

 

CREATE TABLE dizsweb (

    dizsweb DATETIMEOFFSET(7)

);

INSERT INTO dizsweb (dizsweb)
VALUES(
       CAST('2019-02-28 01:45:00.0000000 -08:00' AS DATETIMEOFFSET));