Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 4th, 2004, 06:34 AM
Registered User
 
Join Date: Sep 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default UNION and ORDER BY

SELECT <some_columns>
FROM <some_tables>
ORDER BY <some_columns>
LIMIT 25,25
UNION
SELECT <some_columns>
FROM <some_tables>

but MySql says i'm using worng UNION and ORDER BY

What can i do?

  #2 (permalink)  
Old September 4th, 2004, 01:13 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

ORDER BY cannot be used with the inner select statements that are used for UNION.

You got to use that all the last, because, each select's result is combined together using union operator and ORDER BY can only be applied to the final result set, not for intermediate results. So shift your ORDER BY statement to the last as shown below.
Code:
SELECT <some_columns>
FROM <some_tables>
LIMIT 25,25
UNION 
SELECT <some_columns>
FROM <some_tables>
ORDER BY <some_columns>
_________________________
- Vijay G
Strive for Perfection
  #3 (permalink)  
Old September 5th, 2004, 09:46 AM
Registered User
 
Join Date: Sep 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thank you :)

  #4 (permalink)  
Old September 5th, 2004, 11:23 AM
joefawcett's Avatar
Wrox Author
Points: 9,763, Level: 42
Points: 9,763, Level: 42 Points: 9,763, Level: 42 Points: 9,763, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Exeter, , United Kingdom.
Posts: 3,074
Thanks: 1
Thanked 38 Times in 37 Posts
Default

I can't swear by this as it's DB dependent but if you really need to sort individual sections you can use views that are combined -
VW_Customers:
Code:
SELECT TOP 100% Surname, Forenames FROM tblCustomer ORDER BY Surname
VW_Suppliers:
Code:
SELECT TOP 100% Surname, Forenames FROM tblSupplier ORDER BY Surname
Code:
Select * FROM VW_Customers

UNION ALL

Select * FROM VW_Suppliers
--

Joe
  #5 (permalink)  
Old September 5th, 2004, 12:03 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

Would it be possible to invent an ORDER BY column to order on?

Something like this:

SELECT <some_columns>, '1' AS OrderBY
FROM <some_tables>
UNION

SELECT <some_columns>, '2' AS OrderBY
FROM <some_tables>
ORDER BY OrderBy, <some_columns>

This way, when sorting by, say, surname, the matched records from the first SELECT statement will be listed first, sorted by surname, followed by the ordered records from the second select statement.

Or is there something really wrong with this solution?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
  #6 (permalink)  
Old September 5th, 2004, 12:11 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Imar,

I don't see anything wrong with that. That works perfect. BTW, that was good thinking too.

Cheers!

_________________________
- Vijay G
Strive for Perfection
  #7 (permalink)  
Old September 17th, 2004, 09:23 AM
Registered User
 
Join Date: Sep 2004
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default

darcome,
if you are using MySQL (not sure if this would work for other DBs) you can use ORDER BY for each SELECT, you just need to put parentheses around each SELECT statement

http://dev.mysql.com/doc/mysql/en/UNION.html

e.g.

(SELECT <some_columns>
FROM <some_tables>
ORDER BY <some_columns>
LIMIT 25,25)
UNION
(SELECT <some_other_columns>
FROM <some_other_tables>)
ORDER BY <some_other_columns>)

A couple of things to bear in mind are that:
1. Perhaps obviously, each SELECT statement that is being used must contain the same number of columns;
2. The data type of the columns that are used in the first SELECT statement will determine how the data retrieved from subsequent SELECT statements within the UNION are handled.

For instance, if the first column in the first SELECT statement is of type CHAR(5), then even if the first column in the second SELECT statement is of type TEXT, you will only get the first 5 characters of this column regardless of how much data is in it.

A workaround, although I'm sure someone knows a better way is to start the UNION off with a SELECT statement where each column is a type TEXT but doesn't SELECT anything e.g.

(SELECT <some_columns which are TEXT>
FROM <some_tables>
WHERE 1=0)
UNION
(SELECT <some_columns>
FROM <some_tables>
ORDER BY <some_columns>
LIMIT 25,25)
etc etc........

Apparently, this problem has been fixed in version 4.1.1 of MySQL.

cheers

  #8 (permalink)  
Old September 19th, 2004, 11:15 AM
Registered User
 
Join Date: Sep 2004
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

thaks to you too :)

  #9 (permalink)  
Old June 24th, 2009, 07:22 AM
Registered User
 
Join Date: Jun 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Yes, this query works fine but if you only have distinct rows. If you have union from same table but with different filters then you'll have a duplicates in the result set because of the column OrderBY.

Quote:
Originally Posted by Imar View Post
Would it be possible to invent an ORDER BY column to order on?

Something like this:

SELECT <some_columns>, '1' AS OrderBY
FROM <some_tables>
UNION

SELECT <some_columns>, '2' AS OrderBY
FROM <some_tables>
ORDER BY OrderBy, <some_columns>

This way, when sorting by, say, surname, the matched records from the first SELECT statement will be listed first, sorted by surname, followed by the ordered records from the second select statement.

Or is there something really wrong with this solution?

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
  #10 (permalink)  
Old June 24th, 2009, 03:46 PM
Imar's Avatar
Wrox Author
Points: 70,322, Level: 100
Points: 70,322, Level: 100 Points: 70,322, Level: 100 Points: 70,322, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,576 Times in 1,552 Posts
Default

FYI: the thread is almost 5 years old.... ;-)

Imar
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!




Similar Threads
Thread Thread Starter Forum Replies Last Post
UNION query. rupen Access 3 April 28th, 2006 02:49 AM
UNION of Tables neo_jakey Classic ASP Professional 2 January 31st, 2006 03:08 PM
Problem with UNION - Please Help brettk_1 SQL Server 2000 1 November 9th, 2004 08:08 AM
Order By with Union agarwalmk SQL Language 3 February 18th, 2004 11:33 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.