Order by clause

mateenmohd · April 5th, 2004, 11:19 PM

our company has many employees with same designation; they want to show the records
with there designation order by clause

How can display the records in following order by ie.

Resident Engineer
Asst. Resident Engineer
Quantity Surveyor
Inspector
â¦â¦.

And so on.

I want to display records as above mentioned order.
We want to display records as per employee designation.
Higher post employee designation first and then other designation post employees and so onâ¦.

How can use Order by in the following query ?

SELECT employees.empno, employees.name, approvals.contractno, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate
FROM (employees INNER JOIN approvals ON employees.empno = approvals.empno) INNER JOIN assignments ON employees.empno = assignments.empno order by assignments.aposit='Resident Engineer',
assignments.aposit='Asst. Resident Engineer'
assignments.aposit='Quantity Suveyor'
assignments.aposit='Inspector';

regards.

Mateen

Gert · April 6th, 2004, 02:15 AM

I have never tried this method myself, but I saw the following in a book:

Code:

SELECT employees.empno, employees.name, approvals.contractno, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate
FROM (employees INNER JOIN approvals ON employees.empno = approvals.empno) INNER JOIN assignments ON employees.empno = assignments.empno 
order by 
 CASE assignments.aposit
  WHEN 'Resident Engineer'       THEN 1
  WHEN 'Asst. Resident Engineer' THEN 2
  WHEN 'Quantity Suveyor'        THEN 3
  WHEN 'Inspector'               THEN 4
  ELSE                                5
 END,
assignments.aposit

Maybe it will work for you...?

Gert

happygv · April 6th, 2004, 04:08 AM

I am not sure, if your table structure has any column that defines the hierarchy (Grade) of designation. If so, you can use that in Order By Clause.

Else Gert's post can be a good solution for this, I tested it out, that works fine.

Cheers!

-Vijay G

mateenmohd · April 6th, 2004, 06:20 AM

thanks a lot.

it really work.

Mateen

Quote:

quote:Originally posted by Gert
I have never tried this method myself, but I saw the following in a book:

Code:

SELECT employees.empno, employees.name, approvals.contractno, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate
FROM (employees INNER JOIN approvals ON employees.empno = approvals.empno) INNER JOIN assignments ON employees.empno = assignments.empno 
order by 
 CASE assignments.aposit
  WHEN 'Resident Engineer'       THEN 1
  WHEN 'Asst. Resident Engineer' THEN 2
  WHEN 'Quantity Suveyor'        THEN 3
  WHEN 'Inspector'               THEN 4
  ELSE                                5
 END,
assignments.aposit

Maybe it will work for you...?

Gert

mateenmohd · April 6th, 2004, 06:48 AM

thanks for your response.

but there are some problem.
I am using this query on ACCESS database Report.
our table are save on SQL Server 2000, I create
link with the table to the ACCESS Report.

ACCESS not accept the CASE CLAUSE.
it give syntax error when I run this query on Access Report.
How can use this clause in ACCESS Report, show that is
display data according the CASE ?

this query run on sql query anayzer but it give error on
Access Report why, how can solve it ?

Regards.

Mateen

Quote:

quote:Originally posted by Gert
I have never tried this method myself, but I saw the following in a book:

Code:

SELECT employees.empno, employees.name, approvals.contractno, assignments.aposit, assignments.acontract, assignments.astrdate, assignments.aenddate
FROM (employees INNER JOIN approvals ON employees.empno = approvals.empno) INNER JOIN assignments ON employees.empno = assignments.empno 
order by 
 CASE assignments.aposit
  WHEN 'Resident Engineer'       THEN 1
  WHEN 'Asst. Resident Engineer' THEN 2
  WHEN 'Quantity Suveyor'        THEN 3
  WHEN 'Inspector'               THEN 4
  ELSE                                5
 END,
assignments.aposit

Maybe it will work for you...?

Gert