Row_number function in SQL server

Question

0.00/5 (No votes)

See more:

Hi All,

I have a douts about ROW_NUMBER() funcction using sql server,
i have a two sample query like below,
My problem is my fist query is working fine because its avoiding duplicate datas, compare to my actual table data
But my second query is not avoiding duplicate values as well as my SN number always showing 1
but i want to show my SN number should be autoincrement.
So how can i achieve this solution, please help me.

My Actual table data is:

id
----
ajson1
ajson1
ajson3
ajson4

query 1:
select DISTINCT id from json order by id asc
OUTPUT:
id
-----
ajson1
ajson3
ajson4

query 2:
SELECT
(SELECT DISTINCT ROW_NUMBER() OVER(ORDER BY id ASC)) AS SN,
id
FROM json

OUTPUT:
SN id
-- -----
1 ajson1
1 ajson1
1 ajson3
1 ajson4

thanks in advance

What I have tried:

Row_Number function in sql server

Posted 1-Apr-16 1:16am

stellus

Updated 1-Apr-16 1:42am

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Karthik_Mahalingam · Answer 1 · 2016-04-01T01:37:00

Solution 1

try this

SQL

with T(id) as ( select distinct id from json )
SELECT  'RowNo'=ROW_NUMBER()   OVER(ORDER BY id ASC) , id from T

Posted 1-Apr-16 1:37am

Karthik_Mahalingam

CHill60 · Answer 2 · 2016-04-01T01:42:00

Working with what you already have you could do this ...

SQL

SELECT id, min(SN) FROM
(
	SELECT id,
	ROW_NUMBER() OVER(ORDER BY id ASC) AS SN
	FROM json
) src GROUP BY id

Which gets rid of your duplicate and only shows the lowest row number

ajson1	1
ajson3	3
ajson4	4

But if you want an auto-increment row number then you should consider using an IDENTITY column on the table itself. This will always be unique - even if records are deleted and others subsequently added and any specific row will always have the same number. E.g.

SQL

create table json2
(
	sn int identity(1,1),
	id varchar(max)
)
insert into json2 values
('ajson1'),
('ajson1'),
('ajson3'),
('ajson4')

select min(sn), id
from json2
group by id

Notice how the sql is a little simpler

However, if the results you wanted were actually

ajson1	1
ajson3	2
ajson4	3

then the query would just be

SQL

SELECT id, ROW_NUMBER() OVER(ORDER BY id ASC) AS SN
FROM json
group by id