UNION together with 2 ORDER BY

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 together with 2 ORDER BY

Author

Topic

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-10 : 13:27:27

Hi again!
According to BOL we can't use two order by (if we use tw select statements) when using UNION.

Please see this code:

SELECT DISTINCT top 5 
	'Provision' as Typ,
	U.bonus as "Provision/Antal",
	B.saljare_id as Säljarid,
	RTRIM(LTRIM(P.efternamn)) + ', ' + RTRIM(LTRIM(P.fornamn)) as Säljare
FROM personal P
INNER JOIN utbetalning U ON U.personal_id = P.personal_id
INNER JOIN bokning B ON B.saljare_id = U.personal_id
ORDER BY U.bonus desc, säljare

--UNION 

SELECT  top 5
	'Antal Kunder' as Typ,
	COUNT(B.saljare_id) as "Provision/Antal",
	B.saljare_id as Säljarid,
	RTRIM(LTRIM(P.efternamn)) + ', ' + RTRIM(LTRIM(P.fornamn)) as Säljare
FROM Personal P
INNER JOIN Bokning B ON B.saljare_id = P.personal_id
GROUP BY B.saljare_id, P.efternamn, P.fornamn, B.tidpunkt
HAVING '2006-04-30' < DATEADD(dd,-30,getdate())
ORDER BY "Provision/Antal" DESC, säljare

It is very hard (impossible?) to find a unique combination to use in the ORDER BY at the last select statement.

Do you have any tips? (How to solve the UNION-ORDER BY problem or any other solution)

Best Regards,
KFluffie

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-10 : 13:49:12

Maybe you can use


select * from
(...your select 1 UNION select 2...) as t
order by Typ desc, [säljare]

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-10 : 14:05:48

[code]
SELECT Typ,[Provision/Antal],Säljarid,Säljare
FROM
(
SELECT DISTINCT top 5
'Provision' as Typ,
U.bonus as "Provision/Antal",
B.saljare_id as Säljarid,
RTRIM(LTRIM(P.efternamn)) + ', ' + RTRIM(LTRIM(P.fornamn)) as Säljare
FROM personal P
INNER JOIN utbetalning U ON U.personal_id = P.personal_id
INNER JOIN bokning B ON B.saljare_id = U.personal_id
ORDER BY U.bonus desc, säljare
)t

UNION
SELECT Typ,[Provision/Antal],Säljarid,Säljare
FROM
(
SELECT top 5
'Antal Kunder' as Typ,
COUNT(B.saljare_id) as "Provision/Antal",
B.saljare_id as Säljarid,
RTRIM(LTRIM(P.efternamn)) + ', ' + RTRIM(LTRIM(P.fornamn)) as Säljare
FROM Personal P
INNER JOIN Bokning B ON B.saljare_id = P.personal_id
GROUP BY B.saljare_id, P.efternamn, P.fornamn, B.tidpunkt
HAVING '2006-04-30' < DATEADD(dd,-30,getdate())
ORDER BY "Provision/Antal" DESC, säljare
)t1
[/code]

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-10 : 15:16:17

quote:

Originally posted by visakh16


SELECT Typ,[Provision/Antal],Säljarid,Säljare
FROM
(
SELECT DISTINCT top 5 
	'Provision' as Typ,
	U.bonus as "Provision/Antal",
	B.saljare_id as Säljarid,
	RTRIM(LTRIM(P.efternamn)) + ', ' + RTRIM(LTRIM(P.fornamn)) as Säljare
FROM personal P
INNER JOIN utbetalning U ON U.personal_id = P.personal_id
INNER JOIN bokning B ON B.saljare_id = U.personal_id
ORDER BY U.bonus desc, säljare
)t

UNION 
SELECT Typ,[Provision/Antal],Säljarid,Säljare
FROM
(
SELECT  top 5
	'Antal Kunder' as Typ,
	COUNT(B.saljare_id) as "Provision/Antal",
	B.saljare_id as Säljarid,
	RTRIM(LTRIM(P.efternamn)) + ', ' + RTRIM(LTRIM(P.fornamn)) as Säljare
FROM Personal P
INNER JOIN Bokning B ON B.saljare_id = P.personal_id
GROUP BY B.saljare_id, P.efternamn, P.fornamn, B.tidpunkt
HAVING '2006-04-30' < DATEADD(dd,-30,getdate())
ORDER BY "Provision/Antal" DESC, säljare
)t1

Hello Visakh!
Thanks for your reply. Informational it looks very good but the ordering seems a little bit backwards.

FOr example:
This query:

SELECT  top 5
	'Antal Kunder' as Typ,

Comes above the other (but it still the bottom of the query) and the two ORDER BY columns (U.bonus desc and "Provision/Antal") contains the correct data but it is displayed in the wrong way - it is displayed from bottom to top instead of bottom to top.

1
2
3

instead of (for U.bonus DESC):
3
2
1

However, this is of course easily fixed with:
ORDER BY "Provision/Antal" desc, Typ desc

at the "outer query".

Thanks a lot for your help!

Best Regards,
KF

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-11 : 01:34:07

welcome

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-12 : 06:53:41

I do have some continuing problem when creating a view of my query:

CREATE VIEW uppgift3
AS

SELECT TOP (100) PERCENT Typ,[Provision/Antal],Säljarid,Säljare
FROM
(
SELECT DISTINCT top 5 
	'Provision' as Typ,
	U.bonus as "Provision/Antal",
	B.saljare_id as Säljarid,
	RTRIM(LTRIM(P.efternamn)) + ', ' + RTRIM(LTRIM(P.fornamn)) as Säljare
FROM personal P
INNER JOIN utbetalning U ON U.personal_id = P.personal_id
INNER JOIN bokning B ON B.saljare_id = U.personal_id
ORDER BY U.bonus desc, säljare
)t

UNION 
SELECT TOP (100) PERCENT Typ,[Provision/Antal],Säljarid,Säljare
FROM
(
SELECT  top 5
	'Antal Kunder' as Typ,
	COUNT(B.saljare_id) as "Provision/Antal",
	B.saljare_id as Säljarid,
	RTRIM(LTRIM(P.efternamn)) + ', ' + RTRIM(LTRIM(P.fornamn)) as Säljare
FROM Personal P
INNER JOIN Bokning B ON B.saljare_id = P.personal_id
GROUP BY B.saljare_id, P.efternamn, P.fornamn, B.tidpunkt
HAVING '2006-04-30' < DATEADD(dd,-30,getdate())
ORDER BY "Provision/Antal" DESC, säljare
)t1 ORDER BY "Provision/Antal" desc, Typ desc

If I run the SELECT-query manually, this is my output:

Typ          Provision/Antal                         Säljarid    Säljare
------------ --------------------------------------- ----------- ------------------------------------------
Provision    29000.00                                6           Adamnsson, Esbjörn
Provision    29000.00                                22          Arneson, Brittney
Provision    25000.00                                7           Barksdale, Lorriane
Provision    25000.00                                18          Loomis, Hassan
Provision    25000.00                                38          Baum, Zoe
Antal Kunder 27.00                                   6           Adamnsson, Esbjörn
Antal Kunder 26.00                                   35          Mendoza, Soo
Antal Kunder 24.00                                   18          Loomis, Hassan
Antal Kunder 23.00                                   7           Barksdale, Lorriane
Antal Kunder 23.00                                   13          Ahlgren, Maria

When I run the view I receive this output (Correct data, incorrect order):
SELECT * FROM Uppgift3

Typ          Provision/Antal                         Säljarid    Säljare
------------ --------------------------------------- ----------- ------------------------------------------
Antal Kunder 23.00                                   7           Barksdale, Lorriane
Antal Kunder 23.00                                   13          Ahlgren, Maria
Antal Kunder 24.00                                   18          Loomis, Hassan
Antal Kunder 26.00                                   35          Mendoza, Soo
Antal Kunder 27.00                                   6           Adamnsson, Esbjörn
Provision    25000.00                                7           Barksdale, Lorriane
Provision    25000.00                                18          Loomis, Hassan
Provision    25000.00                                38          Baum, Zoe
Provision    29000.00                                6           Adamnsson, Esbjörn
Provision    29000.00                                22          Arneson, Brittney

A solution to this is to write:

SELECT * FROM Uppgift3
ORDER BY "Provision/Antal" desc, Typ desc

however, I would only like to use the select statement to make the view so easily accessable as possible.

Why is not the information displayed as I write it in when I created the view?

I tried to look at BOL (http://msdn.microsoft.com/en-us/library/aa258253.aspx) but couldn't unfortunately find anything.

Best Regards,
KFluffie

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2009-01-12 : 06:59:05

You can't put the order by in the view and guarantee the order. You need to put it in the select every time. There is no way around this.

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-12 : 07:03:44

quote:
Originally posted by LoztInSpace

You can't put the order by in the view and guarantee the order. You need to put it in the select every time. There is no way around this.

Hi!
Okay, just for me to accept it then =)

Thanks for your answer!

Best Regards,
KF

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-01-12 : 08:26:23

This should work I think,


CREATE VIEW uppgift3
AS
select top 100 percent * from (
SELECT TOP (100) PERCENT Typ,[Provision/Antal],Säljarid,Säljare
FROM
(
SELECT DISTINCT top 5 
	'Provision' as Typ,
	U.bonus as "Provision/Antal",
	B.saljare_id as Säljarid,
	RTRIM(LTRIM(P.efternamn)) + ', ' + RTRIM(LTRIM(P.fornamn)) as Säljare
FROM personal P
INNER JOIN utbetalning U ON U.personal_id = P.personal_id
INNER JOIN bokning B ON B.saljare_id = U.personal_id
ORDER BY U.bonus desc, säljare
)t

UNION 
SELECT TOP (100) PERCENT Typ,[Provision/Antal],Säljarid,Säljare
FROM
(
SELECT  top 5
	'Antal Kunder' as Typ,
	COUNT(B.saljare_id) as "Provision/Antal",
	B.saljare_id as Säljarid,
	RTRIM(LTRIM(P.efternamn)) + ', ' + RTRIM(LTRIM(P.fornamn)) as Säljare
FROM Personal P
INNER JOIN Bokning B ON B.saljare_id = P.personal_id
GROUP BY B.saljare_id, P.efternamn, P.fornamn, B.tidpunkt
HAVING '2006-04-30' < DATEADD(dd,-30,getdate())
ORDER BY "Provision/Antal" DESC, säljare
)t1 ORDER BY "Provision/Antal" desc, Typ desc
)t order by "Provision/Antal" , Typ desc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 08:51:30

Still you cant guarantee that results will be in the order specified in view when you use
select * from view
the order needs to be specified explicitly as
select * from view order by yourcolumns...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-12 : 09:22:28

[code]SELECT DISTINCT TOP 5
'Provision' as Typ,
U.bonus as [Provision/Antal],
B.saljare_id as Säljarid,
RTRIM(LTRIM(P.efternamn)) + ', ' + RTRIM(LTRIM(P.fornamn)) as Säljare
FROM personal AS P
INNER JOIN utbetalning U ON U.personal_id = P.personal_id
INNER JOIN bokning B ON B.saljare_id = U.personal_id
ORDER BY U.bonus desc,
säljare

UNION ALL

SELECT TOP 5 'Antal Kunder' as Typ,
COUNT(B.saljare_id) as [Provision/Antal],
B.saljare_id as Säljarid,
RTRIM(LTRIM(P.efternamn)) + ', ' + RTRIM(LTRIM(P.fornamn)) as Säljare
FROM Personal AS P
INNER JOIN Bokning B ON B.saljare_id = P.personal_id
WHERE '2006-04-30' < DATEADD(dd, -30, getdate())
GROUP BY B.saljare_id,
P.efternamn,
P.fornamn,
B.tidpunkt

ORDER BY 2 DESC,
4[/code]

E 12°55'05.63"
N 56°04'39.26"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 11:31:21

also see this

http://msdn.microsoft.com/en-us/library/ms188385.aspx

it clearly suggests below

When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

kfluffie
Posting Yak Master

103 Posts

Posted - 2009-01-12 : 12:17:41

quote:
Originally posted by visakh16

also see this

http://msdn.microsoft.com/en-us/library/ms188385.aspx

it clearly suggests below

When ORDER BY is used in the definition of a view, inline function, derived table, or subquery, the clause is used only to determine the rows returned by the TOP clause. The ORDER BY clause does not guarantee ordered results when these constructs are queried, unless ORDER BY is also specified in the query itself.

Thank you all for input. The conclusion is simple - Use "ORDER BY" together with the view! =)

Best Regards,
KF

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-12 : 12:21:47

yeah...as simple as that

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources