Solved

ordering the output of rows - oracle query

Posted on 2016-10-06
6
21 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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

Question has a verified solution.

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

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 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 shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

860 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