Order By clause with UNION : SQL Server 2000 
Author Message
 Order By clause with UNION : SQL Server 2000

Hi,
  I have a sql statement which looks like under:

select ActivityTypeID, Description from tblActivityTypes
where PrimaryOption = 1
--order by Description
Union All
select null as ActivityTypeID,replicate('-',20) as
Description
Union All
select ActivityTypeID, Description from tblActivityTypes
where (PrimaryOption is null or PrimaryOption <>1)
--order by Description

The requirement is the first and the last statement should
be ordered by its description.  The result set should look
as under:

ActivityTypeID      Description
5                   Apple
2                   Bat
null                -----------
10                  Andrew
5                   Bob

An early reply would be appreciated.
Regards,
Venugopal



Sun, 05 Jun 2005 23:32:15 GMT
 Order By clause with UNION : SQL Server 2000

You have to add a sortcolumn in your SELECT list like:

SELECT 1 AS sortCol, col1, col2
  FROM tbl1
 UNION ALL
SELECT 2, Null, '-'
 UNION ALL
SELECT 3, col1, col2
  FROM tbl2
 ORDER BY sortCol, col1, col2

--
- Anith
(Please respond only to newsgroups)



Sun, 05 Jun 2005 23:34:52 GMT
 Order By clause with UNION : SQL Server 2000
Thanks Anith that works
Regards,
Venugopal
Quote:
>-----Original Message-----
>You have to add a sortcolumn in your SELECT list like:

>SELECT 1 AS sortCol, col1, col2
>  FROM tbl1
> UNION ALL
>SELECT 2, Null, '-'
> UNION ALL
>SELECT 3, col1, col2
>  FROM tbl2
> ORDER BY sortCol, col1, col2

>--
>- Anith
>(Please respond only to newsgroups)

>.



Sun, 05 Jun 2005 23:57:54 GMT
 Order By clause with UNION : SQL Server 2000
ORDER BY applies to the complete result set of a UNION, there is a trick
however that you can use if you include an extra column that describes which
part of the of the UNION clause the row results from:

select ActivityTypeID, Description, 1 AS subset_nr from tblActivityTypes
where PrimaryOption = 1
--order by Description
Union All
select null as ActivityTypeID,replicate('-',20) as
Description, 2
Union All
select ActivityTypeID, Description, 3 from tblActivityTypes
where (PrimaryOption is null or PrimaryOption <>1)
--order by Description
ORDER BY subset_nr, Description

--
Jacco Schalkwijk MCDBA, MCSD, MCSE
Database Administrator
Eurostop Ltd.


Quote:
> Hi,
>   I have a sql statement which looks like under:

> select ActivityTypeID, Description from tblActivityTypes
> where PrimaryOption = 1
> --order by Description
> Union All
> select null as ActivityTypeID,replicate('-',20) as
> Description
> Union All
> select ActivityTypeID, Description from tblActivityTypes
> where (PrimaryOption is null or PrimaryOption <>1)
> --order by Description

> The requirement is the first and the last statement should
> be ordered by its description.  The result set should look
> as under:

> ActivityTypeID      Description
> 5                   Apple
> 2                   Bat
> null                -----------
> 10                  Andrew
> 5                   Bob

> An early reply would be appreciated.
> Regards,
> Venugopal



Sun, 05 Jun 2005 23:44:18 GMT
 Order By clause with UNION : SQL Server 2000
SELECT
    ActivityTypeID, Description, rank = 1
FROM
    tblActivityTypes WHERE PrimaryOption = 1
UNION ALL
SELECT
    NULL, REPLICATE('-',20), 2
UNION ALL
SELECT
    ActivityTypeID, Description, 3
FROM
    tblActivityTypes WHERE (PrimaryOption != 1)
ORDER BY 3,2

--
Aaron Bertrand, MVP
http://www.aspfaq.com/

FREE server diagnostics component:
http://www.perfhound.com/


Quote:
> Hi,
>   I have a sql statement which looks like under:

> select ActivityTypeID, Description from tblActivityTypes
> where PrimaryOption = 1
> --order by Description
> Union All
> select null as ActivityTypeID,replicate('-',20) as
> Description
> Union All
> select ActivityTypeID, Description from tblActivityTypes
> where (PrimaryOption is null or PrimaryOption <>1)
> --order by Description



Sun, 05 Jun 2005 23:41:02 GMT
 Order By clause with UNION : SQL Server 2000

select ActivityTypeID, Description from tblActivityTypes, rank = 1
where PrimaryOption = 1
Description
Union All
select null as ActivityTypeID,replicate('-',20) as, rank = 2
Description
Union All
select ActivityTypeID, Description from tblActivityTypes, rank = 3
where (PrimaryOption is null or PrimaryOption <>1)
order by rank, Description

AMB



Mon, 06 Jun 2005 00:04:20 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. Order results when using the FOR XML clause in SQL Server 2000

2. Using UNION and ORDER BY clauses

3. Help : Order by with Union clause

4. UNION and ORDER BY clause

5. order by clause not working with union

6. SQL Optimisation , FROM Clause table order, Where clause etc

7. Stored procedures and UNION (SQL Server 2000)

8. union for sql server 2000 problem?

9. Top N and UNION, SQL Server 2000

10. Win2KSrv + Site Server + SQL Server 2000 Install Order

11. sql server order clauses not making sense

12. Custom sort order in sql server 2000!!!!


 
Powered by phpBB® Forum Software