Solved

ordering the output of rows - oracle query

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

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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 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
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

707 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

16 Experts available now in Live!

Get 1:1 Help Now