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

x
Solved

# ordering the output of rows - oracle query

Posted on 2016-10-06
Medium Priority
27 Views
Id like to order the output of the first_result, second_result, and third_result rows so they are written in order from the highest value to the lowest value.  For example, if the values in the first row are 18, 20, 1, 21, 17, 1 then they should be listed in the row as 21, 20, 18, 17, 1, 1
Is this possible?  If so how
Thank so much!!

``````SELECT grade_level,
MAX(CASE WHEN rn = 1 THEN cnt END) first_result,
MAX(CASE WHEN rn = 2 THEN cnt END) second_result,
MAX(CASE WHEN rn = 3 THEN cnt END) third_result,
SUM(cnt)                         total_enrolled
FROM (SELECT q.*, ROW_NUMBER() OVER(PARTITION BY grade_level ORDER BY home_room) rn
FROM (  SELECT grade_level, home_room, COUNT(*) cnt
FROM students
WHERE enroll_status = 0 AND schoolid IN (3)
``````
0
Question by:Basssque
[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
• 3
• 2

LVL 77

Expert Comment

ID: 41831997
Try changing:
ROW_NUMBER() OVER(PARTITION BY grade_level ORDER BY home_room) rn

to:
ROW_NUMBER() OVER(PARTITION BY grade_level ORDER BY home_room, cnt desc) rn
0

LVL 35

Expert Comment

ID: 41832035
Why do you order by home_room?  Shouldn't it be

``````SELECT  grade_level ,
MAX(CASE WHEN rn = 1 THEN cnt
END) first_result ,
MAX(CASE WHEN rn = 2 THEN cnt
END) second_result ,
MAX(CASE WHEN rn = 3 THEN cnt
END) third_result ,
SUM(cnt) total_enrolled
FROM    ( SELECT    q.* ,
ROW_NUMBER() OVER ( PARTITION BY grade_level, home_room ORDER BY cnt DESC ) rn
home_room ,
COUNT(*) cnt
FROM      students
WHERE     enroll_status = 0
AND schoolid IN ( 3 )
GROUP BY  home_room ,
) q
)
``````
0

Author Comment

ID: 41832179
slightvw
the change doesn't have any impact on the order or the values in the rows

ste5an
your change now only outputs the first_result column and all others are null
0

LVL 77

Assisted Solution

slightwv (䄆 Netminder) earned 2000 total points
ID: 41832199
Sorry but I don't know your data.  If you understand what the ROW_NUMBER function is doing, you should be able to change the order yourself.

Maybe move the count before home room or remove home room all together?

ROW_NUMBER() OVER(PARTITION BY grade_level ORDER BY cnt desc) rn

Only you understand your data.  Unless you can post some sample data and expected results, we can only guess.
0

Accepted Solution

Basssque earned 0 total points
ID: 41832224
Figured it out, thanks!
ROW_NUMBER() OVER(PARTITION BY grade_level ORDER BY cnt DESC, home_room) rn
0

Author Closing Comment

ID: 41838098
figured it out based on guidance in assisted solution
0

## Featured Post

Question has a verified solution.

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

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
###### Suggested Courses
Course of the Month7 days, 8 hours left to enroll