Question on SQL. (Sorting data)..

From: Basavaraj <bm_satanal_at_hotmail.com>
Date: 1997/09/28
Message-ID: <342E664C.6D49@hotmail.com>#1/1

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 Sun Sep 28 1997 - 00:00:00 CDT