Learning Learning - 5 months ago 47
SQL Question

How to generate unique primary key values with union all clause on same table with sql query?

I have this query which return Pk of my tables like this:

select Id from employee

1
2
3
4
etc...


Now if i add 1 union all clause then i want output like this in which there should be no repetation of primary key values:

1
2
3
4
5
6
7
8


But when i add 1 more union all clause i want to display output like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14


I have think of this but got repetation:

select Id from employee
union all
select Id + 1 from employee


Then i thought of max function but still didnt work out:

select Id from employee
union all
select (max(Id) + 1) from employee


This is 1 sample fiddle i have created:Sample Fiddle

Is this possible to do this with just sql query??

Answer

You can use ROW_NUMER() like following. The answer is valid for MS-SQL, per my knowledge.

SELECT ROW_NUMBER() OVER (ORDER BY A.ID) AS EmployeeID
FROM 
(
SELECT ID FROM employee
UNION ALL
SELECT ID FROM employee
) A
Comments