Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
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
 
Old December 6th, 2006, 09:03 AM
Authorized User
 
Join Date: Mar 2006
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default UNION queries and sorted data

Hi,
I got a SQL statemant with 3 UNION statements. The problem is it sorts the data and gives the output. I want the output in the order it has been added to a particular Table. How can i do this..? The entries in that table is not in the order of the Primary Key, is there any way out .. ?

-saum

__________________
------------------------------------------------------------------------------------------------------------
<b><i>Adventure, Play, Alive, Fast, Race, Addict, React, Attack, Heat, Aggression, Blast, Ahead, Escape, Challenge, Anarchy, Game, Real, Damage, Insane, Acclerate,Passion, Fear, Dominate, Crazy, Awesome, Sweat, Habit, Avenge, Balls, Imagine, Viril, Chaos, Amuse, Heavy, Panic, @#!%, Fanatic </i></b>
 
Old December 6th, 2006, 09:24 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

The resultset of a UNION query can be sorted via the ORDER BY clause, like any other SELECT query.

Simply specify the column(s) you want to order by in an ORDER BY clause...
Code:
 SELECT a, b, c
   FROM T1
UNION
 SELECT x, y, z
   FROM T2
UNION
 SELECT j, k, l
   FROM T3
ORDER BY b
will sort the resultset by the second column (the names are given by the columns in the first SELECT statement).

Jeff Mason
Custom Apps, Inc.
je.mason@comcast.net
 
Old December 6th, 2006, 10:05 AM
Authorized User
 
Join Date: Mar 2006
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I know that the results can be sorted with the ORDER BY clause(using UNION sorts the data anyways), but that is the issue, i dont want it to be sorted by any particular column, i just want the output to be in the same order as the DATA RESIDES IN THE TABLE, which is not the order in which the Primary Key is generated. So is there any ways this can be achieved .. ?

 
Old December 6th, 2006, 10:16 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 839
Thanks: 0
Thanked 1 Time in 1 Post
Default

Using UNION does NOT "...sort the data anyway...." despite how it may look to you on any given day. If you do not sort a resultset via an ORDER BY clause, the query processor is free to return the data in any old order it wants.

The rows in a table are considered to be an unordered set. This means the rows in a table are in no particular order. This is a fundamental concept in a relational database. This means that the order that the "DATA RESIDES IN THE TABLE" (no need to shout) is a meaningless concept.

If you want the data ordered in a particular way, you'll have to be explicit about it.


Jeff Mason
Custom Apps, Inc.
je.mason@comcast.net
 
Old December 6th, 2006, 10:31 AM
Authorized User
 
Join Date: Mar 2006
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hey Jeff, I didnt mean to offend u - i'm sorry if u did get offended, the caps stmnt was just that i didnt want any1 reading the post to overlook it, as far as sorting by union is concerned - i suppose its a feature in oracle or so as i read somewhere during my quest for a solution i 'google' .. As far as the data in the table is considered, why is a simple (SELECT * with no condition) query on that table o/p's in a particular order...? That's also my dillema - i somehow need the data o/p to be in that fashion, which i believe is not in any explicit order..

 
Old December 6th, 2006, 11:34 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Quote:
quote:Originally posted by nkrust
 Hey Jeff, I didnt mean to offend u - i'm sorry if u did get offended, the caps stmnt was just that i didnt want any1 reading the post to overlook it, as far as sorting by union is concerned - i suppose its a feature in oracle or so as i read somewhere during my quest for a solution i 'google' .. As far as the data in the table is considered, why is a simple (SELECT * with no condition) query on that table o/p's in a particular order...? That's also my dillema - i somehow need the data o/p to be in that fashion, which i believe is not in any explicit order..
 Believe it or not, Jeff Mason is correct: The data in a database table is NOT STORED IN ANY PARTICULAR ORDER. (Notice that I am shouting). Although every time you retrieve that data (without ordering it) it might appear to be in the same order, it has nothing to do with the "order" it is stored in. This is a fundamental principle of relational databases. The "order" of the records has no meaning to the database itself - it stores the data using algorithms that are purposeful to the designers of the database for the purpose of ease of retrieval, high performance for inserting data, or efficient use of rescources. And when data is retrieved, the database engine might use the part of the results of some recent processing that it has cached rather than doing the work all over again - and that might result in an order you don't expect. There are numerous approaches used by the db vendors to improve the performance of their product and you cannot count on the order of the results from qurey to query, even though this might be what the results seemingly are.

Also, each version, patch, or update you get might change the internal workings of something you anecdotally expect.

You are superimposing your idea of what a "table" is (which we all have, but which is only a conceptual picture so humans can think about the data) over the reality of how the database does its work. In other words, this is a good time for you to be discovering the rules of how databases do this sort of thing.

In some databases, you can provide information about which indexes you want used to do certain parts of the work, and this might help in your case.

So... show us your code so we can see why you are getting things sorted in a way you don't expect.

Woody Z
http://www.learntoprogramnow.com
 
Old December 7th, 2006, 07:28 AM
Authorized User
 
Join Date: Mar 2006
Posts: 31
Thanks: 0
Thanked 0 Times in 0 Posts
Default

SELECT
    s.name,
    s.ID,
    s.sacro,
    NVL(CONCAT(CONCAT(p1.fname,' '), p1.lname),'NA'),
    s.desc,
    CONCAT(CONCAT(p2.fname,' '), p2.lname),
    sr.srrate,
    CONCAT(CONCAT(p.fname,' '), p.lname),
    p.email,
    DECODE(a.assgIT,10,'Tabealux'),
    (SELECT COUNT(*) FROM dspch WHERE ID = a.ID AND pID = p.pID AND isPrim =1)
FROM
    people p,
    assg a,
    sys s,
    people p1,
    people p2,
    sysRt sr
WHERE
    s.pID = p1.pID(+) AND
    s.ID = sr.ID AND
    sr.pID = p2.pID AND
    s.status = 1 AND
    a.ID = s.ID AND
    a.pID = p.pID AND
    a.assgStts = 1 AND
    p.pstatus <> -1 AND
    a.assgIT = 10 AND
    1 = ( SELECT COUNT (*) FROM assg WHERE pID = P.pID AND
    assg.assgStts = 1 AND assg.assgIT = 9 AND assg.ID=a.ID)
GROUP BY
    s.name,
    s.ID,
    s.sacro,
    NVL(CONCAT(CONCAT(p1.fname,' '), p1.lname),'NA'),
    s.desc,
    CONCAT(CONCAT(p2.fname,' '), p2.lname),
    sr.srrate,
    CONCAT(CONCAT(p.fname,' '), p.lname),
    p.email,
    a.assgIT,
    a.ID ,
    p.pID

UNION

SELECT
    s.name,
    s.ID,
    s.sacro,
    NVL(CONCAT(CONCAT(p1.fname,' '), p1.lname),'NA'),
    s.desc,
    CONCAT(CONCAT(p2.fname,' '), p2.lname),
    sr.srrate,
    CONCAT(CONCAT(p.fname,' '), p.lname),
    p.email,
    DECODE(a.assgIT,10,'Blanx'),
    (SELECT COUNT (*) FROM dspch WHERE ID = a.ID AND pID = p.pID AND isPrim =1)
FROM
    people p,
    assg a,
    sys s,
    people p1,
    people p2,
    sysRt sr
WHERE
    s.pID = p1.pID(+) AND
    s.ID = sr.ID AND
    sr.pID = p2.pID AND
    s.status = 1 AND
    a.ID = s.ID AND
    a.pID = p.pID AND
    a.assgStts = 1 AND
    p.pstatus <> -1 AND
    a.assgIT = 10 AND
    0 = ( SELECT COUNT(*) FROM assg WHERE pID = P.pID AND
    assg.assgStts = 1 AND assg.assgIT = 9 AND assg.ID=a.ID)
GROUP BY
    s.name,
    s.ID,
    s.sacro,
    NVL(CONCAT(CONCAT(p1.fname,' '), p1.lname),'NA'),
    s.desc,
    CONCAT(CONCAT(p2.fname,' '), p2.lname),
    sr.srrate,
    CONCAT(CONCAT(p.fname,' '), p.lname),
    p.email,
    a.assgIT,
    a.ID ,
    p.pID

UNION

SELECT
    s.name,
    s.ID,
    s.sacro,
    NVL(CONCAT(CONCAT(p1.fname,' '), p1.lname),'NA'),
    s.desc,
    CONCAT(CONCAT(p2.fname,' '), p2.lname),
    sr.srrate,
    CONCAT(CONCAT(p.fname,' '), p.lname),
    p.email,
    DECODE(a.assgIT,9,'Effa'),
    (SELECT count(*) FROM dspch WHERE ID = a.ID AND pID = p.pID AND isPrim =1)
FROM
    people p,
    assg a,
    sys s,
    people p1,
    people p2,
    sysRt sr
WHERE
    s.pID = p1.pID(+) AND
    s.ID = sr.ID AND
    sr.pID = p2.pID AND
    s.status = 1 AND
    a.ID = s.ID AND
    a.pID = p.pID AND
    a.assgStts = 1 AND
    p.pstatus <> -1 AND
    a.assgIT = 9 AND
    0 = ( SELECT COUNT(*) FROM assg WHERE pID = P.pID AND
    assg.assgStts = 1 AND assg.assgIT = 10 AND assg.ID=a.ID)
GROUP BY
    s.name,
    s.ID,
    s.sacro,
    NVL(CONCAT(CONCAT(p1.fname,' '), p1.lname),'NA'),
    s.desc,
    CONCAT(CONCAT(p2.fname,' '), p2.lname),
    sr.srrate,
    CONCAT(CONCAT(p.fname,' '), p.lname),
    p.email,
    a.assgIT,
    a.ID ,
    p.pID



The query sorts by the 1st or the 2nd field (marked red) depending on whish is the 1st column in the SELECT statement. I'm hoping to find some help now.
 
Old December 7th, 2006, 11:40 AM
Friend of Wrox
 
Join Date: May 2006
Posts: 643
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Help to do what?

Woody Z
http://www.learntoprogramnow.com





Similar Threads
Thread Thread Starter Forum Replies Last Post
Union of Two data Sets tusharnarayan SQL Language 7 November 4th, 2008 05:23 PM
How to get least value numbers in a sorted array? ashokparchuri Other Programming Languages 3 December 5th, 2006 09:25 AM
Combining Queries or results from 2 queries Ford SQL Server 2000 24 November 7th, 2005 08:54 PM
data source and SQL queries sss22 ASP.NET 1.0 and 1.1 Basics 1 October 24th, 2005 01:13 PM
sorted table crmpicco Javascript How-To 0 March 17th, 2005 10:29 AM





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