Salta Salta - 3 months ago 14
SQL Question

Row_number function sql

How to do I write a query so it will do as its shown on the screenshot.

I used row_number function to create

ID
column

Select ROW_NUMBER () over (Partition by R.BrandName, R.City order by R.City, R.BrandName) as ID,


query result

Answer

I think you need to gruop by the maching rows and have to give an identical number in an incremental order.. if so use dense_rank() instead of row_number().

try with the below script..

--creating a sample table
CREATE TABLE dbo.GroupSet
(ProdDateTime DATETIME,
 BrandName VARCHAR(50),
 [Description]  VARCHAR(50),
 City VARCHAR(10))

 --populatinng data
 INSERT INTO GroupSet
 VALUES  ('2016-06-15 16:10:03.000','13856.11','POL','Ak')
         ,('2016-06-15 14:56:05.000','13856.11','POL','Ak')
         ,('2016-06-15 15:58:22.000','13856.11','POL','Ak')
         ,('2016-05-04 01:02:50.000','30205.18','MUR','Ak')
         ,('2016-05-03 02:15:10.000','30205.18','MUR','Ak')
         ,('2016-05-03 04:23:10.000','30205.18','MUR','Ak')
         ,('2016-05-03 03:32:10.000','30205.18','MUR','Ak')
         ,('2016-06-03 15:31:20.000','30205.18','MUR','Ak')

--Here is the script and the result you wanted to be in the column ID_DenseRank
SELECT  
   ROW_NUMBER () over ( PARTITION BY R.BrandName, R.City order by R.City, R.BrandName) as ID_RNO, -- ROW_NUMBER() result
   DENSE_RANK() over ( order by  R.BrandName, R.City ) as ID_DenseRank, -- DENSE_RANK() result
   R.ProdDateTime,
   R.BrandName,
   R.[Description],
   R.City 
FROM GroupSet R
Comments