query for getting continuous row number when selecting the results by Union ALL

Question

0.00/5 (No votes)

See more:

Hi,

I written the query as below by using that am getting the required results but the Sno column
values are not continuous eg:sno is getting results 1 to 50 for first query and then again it's getting the results 1 to 100 using second query but now i need sno as 1 to 150 results with continuous number

SQL

SELECT ROW_NUMBER() OVER (ORDER BY id desc ) Sno, Id,Title,  from Job as c WITH (NOLOCK)
where  (CountryID=108 and CompanyName='dell')
group by Id, Title, 
union all
SELECT ROW_NUMBER() OVER (ORDER BY id desc ) Sno, Id,Title, from Job as c WITH (NOLOCK)
where  (CountryID!=108 and CompanyName='dell')
group by Id, Title

Please help me how can i get the continuous number

Posted 15-Jul-14 1:11am

Member 10226004

Updated 13-Jul-20 3:54am

v2

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)

Maciej Los · Answer 1 · 2014-07-15T02:05:00

Have a look at sample query:

SQL

SELECT ROW_NUMBER() OVER (ORDER BY ...) AS Sno
FROM (
    SELECT
    ...
    UNION ALL
    SELECT 
    ...
) AS T
ORDER BY Sno

Note: in your example you're trying to get data exactly in this way:

SQL

WHERE (CountryID=108 OR C***ryID!=108) AND CompanyName='dell'

From logical point of view, it's poor idea. Better use query like:

SQL

SELECT ROW_NUMBER() OVER (ORDER BY id desc ) Sno, Id,Title
FROM Job as c WITH (NOLOCK)
WHERE CompanyName='dell'

[EDIT]
In case you want to add custom order, use query like this:

SQL

SELECT ROW_NUMBER() OVER (ORDER BY id desc ) Sno, Id,Title
FROM Job as c WITH (NOLOCK)
WHERE CompanyName='dell'
ORDER BY CASE WHEN CoutryID=108 THEN 0 ELSE CountryID END

[/EDIT]

Member 14888320 · Answer 2 · 2020-07-13T03:53:00

Solution 2

i should use row_number partition in first select statement not second select statement .
Then how can i retrieve output

Posted 13-Jul-20 3:53am

Member 14888320

Comments

CHill60 13-Jul-20 10:34am

If you have a question then use the red "Ask a Question" link at the top of this page. Be sure to read the posting guidelines there.