asked on

2 select statements in 1 result

I have a bunch of data in a single table and I need to get the data out into 1 result so I can bind that to a .net control.

For simplicity let's just say my table has 2 columns:
object_type, object_title

What I want is a query that puts all the rows where object_type=0 at the top, have that ordered by object_title; then after that have it display the rest of the rows ordered by object_title.

Something like...
object_type, object_title
0, aa
0, bb
0 cc
0, dd
0, ee
6, aaaa
19, bbbb
2, cccc
33, dddd
1, eeee

kmslogic

Can you show your exact query results desired given the example table you gave? What kind of .net control do you want to bind to?

ASKER CERTIFIED SOLUTION

appari

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

PortletPaul

a union all of 2 queries should meet this need I believe. However you need to perform the ordering within subqueries, like this (untested)

select
object_type, object_title
from (
            select
            object_type, object_title
            from tableX
            where object_type = 'o' /*(or whatever) */
            order by object_type, object_title
          ) topbit
union all
select
object_type, object_title
from (
            select
            object_type, object_title
            from tableX
            where object_type != 'o' /*(or whatever) */
            order by object_type, object_title
          ) bottombit

Open in new window

bigbigpig

ASKER

My desired results are above in the question, copied below. I'm binding to a GridView. I know I can programatically create the rows in the GridView and add them with a loop but I'd rather just have the SQL query do the work if possible. Thanks!

Here's my desired result. The top results will always be where object_type=0 AND sorted by object_name. Then after the 0's are there I want the rest of the rows sorted by object_name.
object_type, object_title
0, aa
0, bb
0 cc
0, dd
0, ee
6, aaaa
19, bbbb
2, cccc
33, dddd
1, eeee

PortletPaul

Mmm, sorry
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.

PortletPaul

select
object_type, object_title, srtby
from (
            select
            object_type, object_title, 1 as srtby
            from tableX
            where object_type = 'o' /*(or whatever) */
          ) topbit
union all
select
object_type, object_title, srtby
from (
            select
            object_type, object_title, 2 as srtby
            from tableX
            where object_type != 'o' /*(or whatever) */
          ) bottombit
order by srtby, object_type, object_title

Open in new window

produces:

bigbigpig

ASKER

So simple and works great - exactly what I was looking for!