Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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?

Reply With Quote
  #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
Reply With Quote
  #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 :)

Reply With Quote
  #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
Reply With Quote
  #5 (permalink)  
Old September 5th, 2004, 12:03 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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.
Reply With Quote
  #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
Reply With Quote
  #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

Reply With Quote
  #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 :)

Reply With Quote
  #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.
Reply With Quote
  #10 (permalink)  
Old June 24th, 2009, 03:46 PM
Imar's Avatar
Wrox Author
Points: 72,073, Level: 100
Points: 72,073, Level: 100 Points: 72,073, Level: 100 Points: 72,073, Level: 100
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,089
Thanks: 80
Thanked 1,587 Times in 1,563 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!
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 01:52 PM.


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