union all - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

union all

Author

Topic

anjali5
Posting Yak Master

121 Posts

Posted - 2012-09-07 : 00:58:59

I am trying to write a query that has select All at the top and then all other selections. I wrote the union statement for that.

select 'Select All' as user_id from dual

union all

SELECT distinct user_id as user_id

FROM favorites

WHERE user_id is not null

and query_name is not null

order by user_id;

now the result that is returned does not have select all at the top, but because of sorted order I have the userid's starting from a at the top and then select ALL comes as one of the order. Is their any way, I can get select all at the top and then other names in sorted order after that.

arpana patil
Starting Member

24 Posts

Posted - 2012-09-07 : 04:56:31

select ' Select All' as user_id from dual

union all

SELECT distinct user_id as user_id

FROM favorites

WHERE user_id is not null

and query_name is not null

order by user_id;

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2012-09-07 : 05:35:37

looks like pl/sql code,try to post it in oracle forums

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-13 : 04:43:29

Try this

select * from
(
select ' Select All' as user_id from dual
union all
SELECT distinct user_id as user_id
FROM favorites
WHERE user_id is not null
and query_name is not null
) t
order by rownum;

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 10:09:56

[code]
select * from
(
select ' Select All' as user_id,1 AS OrdSeq
union all
SELECT distinct user_id as user_id,2
FROM favorites
WHERE user_id is not null
and query_name is not null
) t
order by OrdSeq,user_id;

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-13 : 10:10:26

quote:
Originally posted by madhivanan

Try this

select * from
(
select ' Select All' as user_id from dual
union all
SELECT distinct user_id as user_id
FROM favorites
WHERE user_id is not null
and query_name is not null
) t
order by rownum;

Madhivanan

Failing to plan is Planning to fail

whats rownum here?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-14 : 07:19:24

quote:
Originally posted by visakh16

quote:
Originally posted by madhivanan

Try this

select * from
(
select ' Select All' as user_id from dual
union all
SELECT distinct user_id as user_id
FROM favorites
WHERE user_id is not null
and query_name is not null
) t
order by rownum;

Madhivanan

Failing to plan is Planning to fail

whats rownum here?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Note that the code posted by OP is ORACLE

Madhivanan

Failing to plan is Planning to fail

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-09-14 : 07:25:44

Looking at the first post I can't find out why it should be ORACLE - please explain.

Too old to Rock'n'Roll too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-14 : 07:27:45

quote:

Note that the code posted by OP is ORACLE

I am really trying to annoy you when I say this Madhivanan, but it could also be that they have a table name DUAL

I have a vendor database (MS SQL Server) that has a table named DUAL in it with just one column (dummy) and one row. And, they have all kinds of queries and scripts that joined to this table. For a long time, I couldn't figure out why they had that table. I felt very inadequate, and thought I was missing out on a great optimization technique.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-14 : 07:29:54

quote:
Originally posted by webfred

Looking at the first post I can't find out why it should be ORACLE - please explain.

Too old to Rock'n'Roll too young to die.

Fred, this --> http://en.wikipedia.org/wiki/DUAL_table
The history section on that page is interesting to read to see why he named it DUAL.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-14 : 07:40:35

quote:
Originally posted by webfred

Looking at the first post I can't find out why it should be ORACLE - please explain.

Too old to Rock'n'Roll too young to die.

People sometimes post questions related to other RDBMSs. So based on the name DUAL I thought it was ORACLE.

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-09-14 : 07:43:25

quote:
Originally posted by sunitabeck

quote:

Note that the code posted by OP is ORACLE
I am really trying to annoy you when I say this Madhivanan, but it could also be that they have a table name DUAL

I have a vendor database (MS SQL Server) that has a table named DUAL in it with just one column (dummy) and one row. And, they have all kinds of queries and scripts that joined to this table. For a long time, I couldn't figure out why they had that table. I felt very inadequate, and thought I was missing out on a great optimization technique.

Ok. In ORACLE everything you SELECT requires a TABLE so SELECTing a constant value is done using a dummy DUAL table

SELECT 1 FROM DUAL

SELECT SYSDATE FROM DUAL

But I don't find a reason to simulate it in SQL Server as you don't need a TABLE reference to SELECT a constant

Madhivanan

Failing to plan is Planning to fail

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-09-14 : 07:57:38

quote:
But I don't find a reason to simulate it in SQL Server as you don't need a TABLE reference to SELECT a constant

In my case, the vendor originally developed the database and application using Oracle, and they found it less traumatic to keep the DUAL database when they wanted to port it to SQL Server.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 10:05:49

quote:
Originally posted by madhivanan

quote:
Originally posted by sunitabeck

quote:

Note that the code posted by OP is ORACLE
I am really trying to annoy you when I say this Madhivanan, but it could also be that they have a table name DUAL

I have a vendor database (MS SQL Server) that has a table named DUAL in it with just one column (dummy) and one row. And, they have all kinds of queries and scripts that joined to this table. For a long time, I couldn't figure out why they had that table. I felt very inadequate, and thought I was missing out on a great optimization technique.

Ok. In ORACLE everything you SELECT requires a TABLE so SELECTing a constant value is done using a dummy DUAL table

SELECT 1 FROM DUAL

SELECT SYSDATE FROM DUAL

But I don't find a reason to simulate it in SQL Server as you don't need a TABLE reference to SELECT a constant

Madhivanan

Failing to plan is Planning to fail

Ok. now i got it

thanks for the info

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources