use of union statement
-
When I run the following query on the pubs database:
select 'this book at top of the list' AS 'maintitle'
select t.title AS 'maintitle'
from titles t, sales s
where t.title_id = s.title_id
order by s.ord_num
I get two results sets.
I would like to join them together into a single results set with the 'this book at top of the list' as the first result.
So I tried the union statement:
select 'this book at top of the list' AS 'maintitle'
union
select t.title AS 'maintitle'
from titles t, sales s
where t.title_id = s.title_id
order by s.ord_num
but it gives a syntax error. The problem is that for union both selects must have the same sort order.
Is there a way to do what I need?
Thanks
-
how about :
select maintitle
from (
select 0 as ordercol, 'this book at top of the list' AS 'maintitle'
union
select 1 as ordercol, t.title AS 'maintitle'
from titles t, sales s
where t.title_id = s.title_id
) t1
order by ordercol, maintitle
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Thanks for that, but the ordering is wrong.
On my SQL 2000 pubs database the first three rows from your query are:
maintitle
--------------------------------------------------------------------------------
this book at top of the list
But Is It User Friendly?
Computer Phobic AND Non-Phobic Individuals: Behavior Variation
whereas from my query it is:
maintitle
----------------------------
this book at top of the list
(1 row(s) affected)
maintitle
--------------------------------------------------------------------------------
The Gourmet Microwave
The Busy Executive's Database Guide
-
I completely overlooked the s.ord_num as being the sortordernumber you wanted to use. That's why I added my own ordercol containing 0 or 1.
how about this
select maintitle
from (
select -1 as ordercol, 'this book at top of the list' AS 'maintitle'
union
select s.ord_num as ordercol, t.title AS 'maintitle'
from titles t, sales s
where t.title_id = s.title_id
) t1
order by ordercol
Johan
Learn to play, play to learn !Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:- How to post Performance Problems
- How to post data/code to get the best help[/url]- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
-
Now I'm getting a:
Server: Msg 245, Level 16, State 1, Line 1
Syntax error converting the varchar value 'QA879.1' to a column of data type int.
I can tell you don't have a SQL server client in front of you
-
select maintitle
from (
select 0 As PrimarySort, '' as ord_num, 'this book at top of the list' AS maintitle
union ALL
select 1, s.ord_num, t.title AS maintitle
from titles t, sales s
where t.title_id = s.title_id
) dt
order by PrimarySort, ord_num
-
Hurrah!
That works perfectly
Many Thanks PW!
-
select 0 AS s.ord_num, 'this book at top of the list' AS 'maintitle'
union
select t.title AS 'maintitle'
from titles t, sales s
where t.title_id = s.title_id
order by s.ord_num
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply