Re: Question on SQL. (Sorting data)..

From: Erich Hurst <erich.hurst_at_compaq.com>
Date: 1997/09/29
Message-ID: <01bccce0$0a8bb580$62d612ac@erichhurst2.im.hou.compaq.com>#1/1

This is just a thought. YMMV:

1. CREATE VIEW DEPT_GRADE AS SELECT DIV_NO, DEPT_NO, SUM(DECODE(GRADE,5,1,0)) GRADE_5_CNT, SUM(DECODE(GRADE,4,1,0)) GRADE_4_CNT, ... FROM EMP GROUP BY DIV_NO, DEPT_NO
2. SELECT EMP.* FROM EMP E, DEPT_GRADE D WHERE E.DIV_NO = D.DIV_NO AND E.DEPT_NO = D.DEPT_NO ORDER BY GRADE_5_CNT DESC, GRADE_4_CNT DESC, ... , E.DIV_NO, E.DEPT_NO, E.GRADE DESC, E.EMP_NO

(I *love* DECODE...)

Basavaraj <bm_satanal_at_hotmail.com> wrote in article <342E664C.6D49_at_hotmail.com>...
> Hi,
>
> I have the following question and a solution to it will be highly
> appreciated.
>
> I am preparing a report using Pro*C/C++ from the data stored in a single
> table "EMP". The data stored in the table is as follows :
>
> Column EMP_NO is the primary key.
>
> ----------------------------------------------------------------------
> EMP_NO DIV_NO DEPT_NO GRADE DATE OF JOINING
> ----------------------------------------------------------------------
> 101 100 10 5
> 103 100 20 5
> 102 100 10 4
> 107 100 10 3
> 104 100 20 3
> 105 100 30 3
> 106 200 10 5
> 108 100 30 2
> 111 100 40 1
> 109 100 40 3
> 110 200 10 4
> 112 200 30 4
> 113 200 30 4
> 115 100 40 1
> 114 200 30 5
> -----------------------------------------------------------------------
>
> Using the above data I would like to group all the data, first on the
> DIV_NO then on the DEPT_NO. On each grouped DEPT_NO a condition will be
> applied to sort the DEPT_NO GROUP on the basis of "HIGHTEST NUMBER OF
> HIGHTEST GRADE". The desired output will be :
>
> ----------------------------------------------------------------------
> EMP_NO DIV_NO DEPT_NO GRADE DATE OF JOINING
> -----------------------------------------------------------------------
> 101 100 10 5
> 102 100 10 4
> 107 100 10 3
> --------------------------
> 103 100 20 5
> 104 100 20 3
> --------------------------
> 105 100 30 3
> 108 100 30 2
> --------------------------
> 109 100 40 3
> 111 100 40 1
> 115 100 40 1
> -----------------------------------------------------------------------
> 114 200 30 5
> 112 200 30 4
> 113 200 30 4
> 106 200 10 5
> 110 200 10 4
> -----------------------------------------------------------------------
>
> Note : Dept_No :30 of Div_No : 100 is above the Dept_No :40 since it has
> the a emp_no : 108 whose grade = 2 which is greater than the
> emp_no : 111 whose grade = 1.
>
> I hope I have made my question clear.
>
> Answer to this is highly appreciated.
>
> Thanx in advance.
> Basavaraj
>
Received on Mon Sep 29 1997 - 00:00:00 CDT