Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 30
  • Last Modified:

ordering the output of rows - oracle query

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
Basssque
Asked:
Basssque
  • 3
  • 2
2 Solutions
 
slightwv (䄆 Netminder) Commented:
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
 
ste5anSenior DeveloperCommented:
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
 
BasssqueAuthor Commented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
slightwv (䄆 Netminder) Commented:
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
 
BasssqueAuthor Commented:
Figured it out, thanks!
ROW_NUMBER() OVER(PARTITION BY grade_level ORDER BY cnt DESC, home_room) rn
0
 
BasssqueAuthor Commented:
figured it out based on guidance in assisted solution
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now