Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > SQL Server > SQL Server 2000 > SQL Server 2000
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
SQL Server 2000 General discussion of Microsoft SQL Server -- for topics that don't fit in one of the more specific SQL Server forums. version 2000 only. There's a new forum for SQL Server 2005.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Server 2000 section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old April 5th, 2004, 11:19 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default Order by clause

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









Reply With Quote
  #2 (permalink)  
Old April 6th, 2004, 02:15 AM
Authorized User
 
Join Date: Jun 2003
Location: , , Norway.
Posts: 95
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #3 (permalink)  
Old April 6th, 2004, 04:08 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Bangalore, KA, India.
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
  #4 (permalink)  
Old April 6th, 2004, 06:20 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
  #5 (permalink)  
Old April 6th, 2004, 06:48 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: abu dbabi, , United Arab Emirates.
Posts: 518
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Order by clause priyan.viji SQL Language 2 December 17th, 2007 02:45 AM
re arrrange nulls in order by clause mat41 SQL Language 13 August 7th, 2007 08:10 PM
order by In clause items? cs8271506 SQL Language 2 September 19th, 2004 08:13 PM
HAVING clause Adam H-W SQL Server ASP 2 February 11th, 2004 01:37 PM
document node order vs sort node order. ladyslipper98201 XSLT 2 June 5th, 2003 11:06 AM



All times are GMT -4. The time now is 09:01 AM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.