Oracle Select in a Select with Order by

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.

wayneatchleyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hnasrCommented:
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
dqmqCommented:
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
hnasrCommented:
What about switching group by:
Example: group by rownum, b.course_number ----> group by b.course_number, rownum
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

sdstuberCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dqmqCommented:
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
sdstuberCommented:
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
sdstuberCommented:
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
wayneatchleyAuthor Commented:
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
sdstuberCommented:
glad I could help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.