query question...

[RAB]

I am trying to figure out a query and any help would be appreciated.

Table A

EmpId as integer PK
EmpName as varchar
EmpAddress as varchar
EmpCity as varchar
EmpState as varchar
Comment as varchar

My query is as follows

select * from Table A

I want to order the query by EmpName descending if any of the comment
fields are "On Leave".
However if there is no comment field of "On Leave" then I want to
order the query by EmpName ascending.

Thanks in advance,
RABMissouri2011

[Robin Tanner]

SELECT *
FROM Table A (NOLOCK)
WHERE Comment = 'On Leave'
ORDER BY EmpName DESC

UNION

SELECT *
FROM TABLE A (NOLOCK)
WHERE Comment != 'On Leave'
ORDER BY EmpName

--
You received this message because you are subscribed to the Google Groups "SQL Server" group.
To post to this group, send email to sql-s...@googlegroups.com.
To unsubscribe from this group, send email to sql-server+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sql-server?hl=en.

--

Thanks

Robin Tanner
801-510-0113

[RoLaAus]

You're joking, right?

At first, I thought this was a simple homework assignment, but I know
that can't be, because you can't change the sort order. You can,
however, have multiple sort orders, or you can create a query that
grabs the "On Leave" first, then the rest.

[Dwight Shackelford]

You'll probably need to do subqueries, sorted the way you want, then union them.

[Foyaz Ahmed]

here is the query

Select * from Table A where Comment  like '%On Leave%' order by Comment , EmpId  desc

On Sat, Mar 12, 2011 at 7:24 AM, RAB <rabmi...@yahoo.com> wrote:

--
You received this message because you are subscribed to the Google Groups "SQL Server" group.
To post to this group, send email to sql-s...@googlegroups.com.
To unsubscribe from this group, send email to sql-server+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sql-server?hl=en.

--
With Thanks
---------------------------------------------
Foyaz Ahmed
CEO
Crystal Technology Bangladesh.
141/4 Abed Dhali Road, Lake Circus
Kalabagnan, Dhaka-1205, Bangladesh
Call : +8801937895898
Web: www.ctechbd.com

[swetha...@gmail.com]

Hope this query would help you...

SELECT * FROM Table A

ORDER BY
CASE
when comment = 'On Leave' then EmpName
END
DESC,
CASE
when comment <> 'On Leave' then EmpName
END
ASC

Regards,
Swetha.