Can I specify the order in an ORDER BY clause in a SQL Statement?

February 19, 2009 9:18 AM

Can I specify the order in an ORDER BY clause in a SQL Statement? If not then what is the best way of doing the following query?

Can I specify the order in an ORDER BY clause in a SQL Statement so that it is neither ASC or DESC?
or if I can't - which I think is the answer after searching in vain on Google - then what is an alternative to solve this seemingly easy problem.

Employee directory. Each employee is assigned to a department. Many but not all employees are assigned to a second department. The directory lists the employees by last name.

What needs to happen is that if a department (say # 3) is queried, all persons who are assigned either primarily or secondarily to that department get listed, but the order must be by Primary department, lastname, THEN just lastname.

Thus if a query is for department 3 then the output must be

Name        DEPT        SECONDARY DEPT
Johnson, J. 3            1
Jones,M.   3
Moss, P .   3             4
Adams, J.  4              3
Johnson.Q  1             3

(note that it doesn't matter what department the secondary users are in, that can be sorted by just last name)

The select statement I have is something like this;

SELECT employee.id, employee.lname, employee.fname, employee.department, employee.secondarydepartment FROM EMPLOYEES WHERE (employees.department = "3" OR employees.secondarydepartment = "3" ORDER BY *huh*? (this is where I am stuck.)

Problem is there are at least six departments, each assigned a number, so using ORDER BY Department won't work if it is in the middle.

I am toying with the ideal of creating a second table, something like employeesaffiliation and then doing a JOIN, complicated, but I think I can do it - unless SQL experts can point me on the right path. I admit I am out of my league with more than simple queries.

posted by xetere to Computers & Internet (9 answers total)

I'm making this up as I go along, but see if this helps:

SELECT employee.department, * FROM employees WHERE employees.department = "3"
UNION
SELECT employee.secondarydepartment, * FROM employees WHERE employees.secondarydepartment = "3"
ORDER BY 1

If you union the two queries together, you can order by a column... in this case, indicated by it's position, 1, rather than its name.
posted by the jam at 9:26 AM on February 19, 2009

What happens when you do

ORDER BY employee.department, employee.lname
posted by avex at 9:28 AM on February 19, 2009

Best answer: You can use a CASE statement in the ORDER BY clause. Something like this should do what you want:

DECLARE @intDepartment int
SET @intDepartment = 3

SELECT employee.id, employee.lname, employee.fname, employee.department, employee.secondarydepartment
FROM EMPLOYEES
WHERE (employees.department = @intDepartment OR employees.secondarydepartment = @intDepartment)
ORDER BY (case when employees.department = @intDepartment then 1 else 2 end), employee.lname
posted by gatorbiddy at 9:29 AM on February 19, 2009

Best answer:

ORDER BY 
   CASE
      WHEN employees.department = '3' THEN 1
      ELSE 2
   END, employee.lname

If you're using SQL to handle your variable substitution, then just replace the '3' with your variable. This assumes you'll only ever be selecting on a single department.
posted by uncleozzy at 9:30 AM on February 19, 2009

Response by poster: What happens when you do

ORDER BY employee.department, employee.lname

Unfortunately not what I want, because we want the deparment querieid for always toe be on top, so for the example above, using department 3, someone who's primary department is 1 but whose secondary department is 3 would appear first, meaning Q. Johnson would appear before J. Johnson.

but I am going to try the jam's suggestion. Didn't know enough about UNION to realize that will work. I'll report back soon.
posted by xetere at 9:32 AM on February 19, 2009

Actually, I goofed that up. Pay no attention to my answer, that won't do it.
posted by the jam at 9:35 AM on February 19, 2009

Response by poster: Well it is on to the CASE statements then. Thanks all.
posted by xetere at 9:36 AM on February 19, 2009

Response by poster: Thank you getorbiddy and uncleozy This is EXACTLY what I needed and works like a charm.
posted by xetere at 9:53 AM on February 19, 2009

The case in the order by statement is extremely database specific. I'd go with the union method, because that's at least compatible with different databases (ie ANSI standard).
posted by BigCalm at 2:23 PM on February 20, 2009