Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle Select in a Select with Order by

Posted on 2008-10-20
9
Medium Priority
?
1,470 Views
Last Modified: 2013-12-19
I need to pivot some data in my query and the way I am doing it seems to work, but I want to make sure I can get consistent results.  I have 2 tables of data with a common key, so here is the query:

select A.ID,
          (Select B.course_title
           from wa.avail_courses B
           where A.ID = B.ID
           group by rownum, b.course_title
           having rownum = 1) as "Course_1",

          (Select B.course_number
           from wa.avail_courses B
           where A.ID = B.ID
           group by rownum, b.course_number
           having rownum = 1) as "Course_number_1",

          (Select B.course_title
           from wa.avail_courses B
           where A.ID = B.ID
           group by rownum, b.course_title
           having rownum = 2) as "Course_2",

          (Select B.course_number
           from wa.avail_courses B
           where A.ID = B.ID
           group by rownum, b.course_number
           having rownum = 2) as "Course_number_2"
from wa.all_courses A

When I run the query, it is correctly pulling the title and course number for each of the inner selects, but I know I can't trust that the group by will always sort the data.  I can't include an order by in the subselects to keep them in line.  So, what is the answer?  

We are using Oracle 9i.

0
Comment
Question by:wayneatchley
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 31

Expert Comment

by:hnasr
ID: 22761674
The sub queries should return only one record.
Here inner query should include a condition for 1st record.

Oracle: (Select * from tbl where rownum=1 )
Access: (Select Top 1 * from tbl )
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22761728
Here's the form to Pivot your results.  I offer it with some caveats. First, it produces 1 row for each course with the appropriate column filled in.  I really doubt that is what you want--usually some sort of summary operation is performed to consolidate the mulitiple rows.  Second, it's generally better to pivot on a meaningful value rather than row ID.  That way you get the same course in the same columns every time you run it.   With rowID you cannot predict the ordering of the courses.  To make the column sequence predictable, you need to use an order by inside an inline view, not in the main select.  


select A.ID
,Case rowId when 1 B.Course_title end "Course_1"
,Case rowID when 1 B.Course_Number end "Course_Number_1"
,Case rowId when 2 B.Course_title end "Course_2"
,Case rowID when 2 B.Course_Number end "Course_Number_2"
...
from wa.all_courses A left join wa.avail_courses B on A.ID = B.ID

0
 
LVL 31

Expert Comment

by:hnasr
ID: 22761777
What about switching group by:
Example: group by rownum, b.course_number ----> group by b.course_number, rownum
0
Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 22761846
something like this perhaps...
  SELECT   A.ID,
           MAX(DECODE(b.rn, 1, b.course_title)) "Course_1",
           MAX(DECODE(b.rn, 1, b.course_number)) "Course_number_1",
           MAX(DECODE(b.rn, 2, b.course_title)) "Course_2",
           MAX(DECODE(b.rn, 2, b.course_number)) "Course_number_2"
    FROM   wa.all_courses A,
           (SELECT   ID, course_title, course_number
              FROM   (SELECT   ID,
                               course_title,
                               course_number,
                               ROW_NUMBER()
                                   OVER (PARTITION BY ID ORDER BY course_title, course_number)
                                   rn
                        FROM   wa.avail_courses)
             WHERE   rn <= 2) b
   WHERE   A.ID = b.ID(+)
GROUP BY   A.ID

Open in new window

0
 
LVL 42

Expert Comment

by:dqmq
ID: 22761964
Oh my gosh..it's DECODE not CASE in Oracle.  For a moment I was wearing the wrong hat.  SdStuber has it right... even has the column sorted by title and number, which addresses one of my caveats.  

Not sure the last GROUP BY and MAX aggregates accomplish anything unless there are duplicate ID's in the data.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22762015
you could do it with case in Oracle too

for simple one-value checks like this I usually use decode though, just to save keystrokes
  SELECT   A.ID,
           MAX(case when b.rn = 1 then b.course_title end) "Course_1",
           MAX(case when b.rn = 1 then b.course_number end) "Course_number_1",
           MAX(case when b.rn = 2 then b.course_title end) "Course_2",
           MAX(case when b.rn = 2 then b.course_number end) "Course_number_2"
    FROM   wa.all_courses A,
           (SELECT   ID, course_title, course_number
              FROM   (SELECT   ID,
                               course_title,
                               course_number,
                               ROW_NUMBER()
                                   OVER (PARTITION BY ID ORDER BY course_title, course_number)
                                   rn
                        FROM   wa.avail_courses)
             WHERE   rn <= 2) b
   WHERE   A.ID = b.ID(+)
GROUP BY   A.ID

Open in new window

0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22762023
I'm not sure why you're trying to do a group by anyway.

what are you "grouping"?  If you are trying to do a distinct,  then use distinct.
0
 

Author Closing Comment

by:wayneatchley
ID: 31507980
In the interim to your answer, I was talking with a DBA about this and he kept saying to "just use row number".  Since I didn't know about the OLAP functions in Oracle, I kept thinking "I am using rownum(ber)... "  Seeing it written out this way makes sense.

Oh yeah, I was using group by in my original question on rownum so I could use having and get rownum = 2... I just felt wierd doing it this way since I didn't know if I could always count on group by doing the sort the same way (after more reading on EE and asktom, I am now very clear that if does not.)
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 22762664
glad I could help
0

Featured Post

URL rewriting in AWS CloudFront

A quick how-to guide to implement with a Lambda function!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question