January 27, 2012 at 5:20 am
Hi All
I feeling very odd to ask but i am really really very confused by this how to make order by when query have union or union all
eg
select 'TestData'
from Relation
order by ChartID
union
select SourceCode
from Relation
where ChartID = 1
order by ChartID
here i am union two set of query and need to order it by chartid
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
January 27, 2012 at 5:24 am
UNION <ALL>
acts a bit like a derived table. You can only put ONE order by and it will by applied after the union has been processed.
January 27, 2012 at 5:26 am
just to add to the previous comment...
The order by, used with a union(all) select can only perform its operation on a column that occurs in the select list !
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
January 27, 2012 at 5:40 am
DECLARE @test-2 TABLE
(
group_id integer NOT NULL,
id integer NOT NULL,
value integer NOT NULL
)
INSERT @test-2
(group_id, id, value)
VALUES
(100, 1, 20),
(100, 2, 40),
(100, 3, 60),
(200, 3, 60),
(200, 2, 40),
(200, 1, 20);
SELECT
t1.id,
t1.value
FROM @test-2 AS t1
WHERE
t1.group_id = 100
UNION
SELECT
t2.id,
t2.value
FROM @test-2 AS t2
WHERE
t2.group_id = 200
ORDER BY
-- Notice name is t1 not t2
t1.value DESC;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 27, 2012 at 5:58 am
No way around it now, I'll have to ask for a urine test Paul.
:hehe:
January 27, 2012 at 6:20 am
Ninja's_RGR'us (1/27/2012)
No way around it now, I'll have to ask for a urine test Paul. :hehe:
?
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 27, 2012 at 6:44 am
Sami,
You cannot order by a non-existent column (in the returned dataset) for this case.
You must put ChartID column in the select.
try this:
--create table relation (ChartID int, SourceCode varchar(100))
GO
--insert into relation values
-- (1,'one')
--,(2,'two')
--,(3,'3')
GO
(
select 'TestData' as [mycolumn],ChartID
from Relation
--order by ChartID
union
select SourceCode as [mycolumn],ChartID
from Relation
where ChartID = 1
)
order by ChartID
GO
--drop table relation
GO
OBS:. The fisrt row is listed twice, review the comparasion between the string 'TestData' and the varchar returned by the [SourceCode] column.
In fact what's the point in order a list of 'TestData' rows?
January 27, 2012 at 7:08 am
Thanks all
I got good lesson to learn about union and order by
Thanks & Regards
Syed Sami Ur Rehman
SQL-Server (Developer)
Hyderabad
Email-sami.sqldba@gmail.com
January 27, 2012 at 7:12 am
To elaborate a bit on Pauls script ...
SELECT
t1.id,
t1.value as Val1 , 't1' as source
FROM @test-2 AS t1
WHERE
t1.group_id = 100
UNION
SELECT
t2.id,
t2.value , 't2' as source
FROM @test-2 AS t2
WHERE
t2.group_id = 200
ORDER BY
-- Notice name is t1 not t2
t1.value DESC;
Result
idVal1source
360t1
360t2
240t1
240t2
120t1
120t2
ORDER BY
Val1 DESC;
Is also OK. ( and is the one I prefer )
ORDER BY
-- Notice name is t2 not t1
t2.value DESC;
results in error:
Msg 4104, Level 16, State 1, Line 33
The multi-part identifier "t2.value" could not be bound.
Msg 104, Level 16, State 1, Line 33
ORDER BY items must appear in the select list if the statement contains a UNION, INTERSECT or EXCEPT operator.
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
January 27, 2012 at 9:13 am
SQL Kiwi (1/27/2012)
Ninja's_RGR'us (1/27/2012)
No way around it now, I'll have to ask for a urine test Paul. :hehe:?
You know too much, you must be alien or something :alien:
January 27, 2012 at 9:15 am
Ninja's_RGR'us (1/27/2012)
You know too much, you must be alien or something :alien:
Ah, I see. Thank you. Actually I know very little, but what I do know seems to come up a lot 🙂
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 27, 2012 at 9:20 am
SQL Kiwi (1/27/2012)
Ninja's_RGR'us (1/27/2012)
You know too much, you must be alien or something :alien:Ah, I see. Thank you. Actually I know very little, but what I do know seems to come up a lot 🙂
Maybe, but the real kicker is that you seem to be the only one who knows it. Hence the alien comment. 😛
January 27, 2012 at 9:43 am
Twisted the query a bit... same output & same execution plan... but looks nicer to me... any thoughts most welcome
Version 1:
DECLARE @test-2 TABLE
(
group_id integer NOT NULL,
id integer NOT NULL,
value integer NOT NULL
)
INSERT @test-2
(group_id, id, value)
VALUES
(100, 1, 20),
(100, 2, 40),
(100, 3, 60),
(200, 3, 60),
(200, 2, 40),
(200, 1, 20);
SELECT
t1.id,
t1.value
FROM @test-2 AS t1
WHERE
t1.group_id = 100
UNION
SELECT
t2.id,
t2.value
FROM @test-2 AS t2
WHERE
t2.group_id = 200
ORDER BY
-- Notice name is t1 not t2
t1.value DESC;
Version 2:
DECLARE @test-2 TABLE
(
group_id integer NOT NULL,
id integer NOT NULL,
value integer NOT NULL
)
INSERT @test-2
(group_id, id, value)
VALUES
(100, 1, 20),
(100, 2, 40),
(100, 3, 60),
(200, 3, 60),
(200, 2, 40),
(200, 1, 20);
select * from
(
SELECT
t1.id,
t1.value
FROM @test-2 AS t1
WHERE
t1.group_id = 100
UNION
SELECT
t2.id,
t2.value
FROM @test-2 AS t2
WHERE
t2.group_id = 200
) t1
ORDER BY
-- Notice name is t1 not t2
t1.value DESC;
January 27, 2012 at 9:50 am
SQL Kiwi (1/27/2012)
Ninja's_RGR'us (1/27/2012)
You know too much, you must be alien or something :alien:Ah, I see. Thank you. Actually I know very little, but what I do know seems to come up a lot 🙂
Actually the most impressive thing is that you can summon it back. I know a lot of stuff but the details are lost after just a few minutes. :w00t:
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply