Solved

ordering the output of rows - oracle query

Posted on 2016-10-06
6
19 Views
Last Modified: 2016-10-11
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)
                  GROUP BY home_room, grade_level) q)
GROUP BY grade_level
ORDER BY grade_level;

Open in new window

0
Comment
Question by:Basssque
  • 3
  • 2
6 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
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 33

Expert Comment

by:ste5an
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
          FROM      ( SELECT    grade_level ,
                                home_room ,
                                COUNT(*) cnt
                      FROM      students
                      WHERE     enroll_status = 0
                                AND schoolid IN ( 3 )
                      GROUP BY  home_room ,
                                grade_level
                    ) q
        )
GROUP BY grade_level
ORDER BY grade_level;

Open in new window

0
 

Author Comment

by:Basssque
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 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

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

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
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.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

911 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now