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

From: Someone <answers_at_erols.com.tyfns>
Date: 1997/09/28
Message-ID: <342F09DE.D1540AF2@erols.com.tyfns>#1/1

This is a multi-part message in MIME format.

--------------C93BB154E8D42F2AC199AC95
Content-Type: text/plain; charset=us-ascii
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Content-Transfer-Encoding: 7bit

Following the is general idea. I am doing this off the top of my head so

check it out. Query is below.

select

     e1.EMP_NO
  ,  e1.DIV_NO
  ,  e1.DEPT_NO
  ,  e1.GRADE
  ,  e1.DATE OF JOINING

  , count (distinct e2.GRADE) a
from
  EMP e1
, EMP e2
where

    e1.div_no = e2.div_no
and

    e1.dept_no = e2.dept_no
order by

      a
  , e1.grade
  , e1.emp_no
group by

     e1.div_no
  , e1.dept_no

Basavaraj wrote:

> 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

--------------C93BB154E8D42F2AC199AC95
Content-Type: text/x-vcard; charset=us-ascii; name="vcard.vcf"
Content-Transfer-Encoding: 7bit
Content-Description: Card for Someone
Content-Disposition: attachment; filename="vcard.vcf"

begin:          vcard
fn:             Someone
n:              ;Someone
email;internet: answers_at_erols.com.tyfns
title:          Important
note:           Remove tyfns from my email address to reply.  TYFNS stands for "Thank you for not spamming."

x-mozilla-cpt: ;0
x-mozilla-html: FALSE
end: vcard

--------------C93BB154E8D42F2AC199AC95-- Received on Sun Sep 28 1997 - 00:00:00 CDT