union and sorting

Hi all, SQL Server question. I have a query like so: ******* SELECT * FROM Table1 WHERE Table1Item  = 'something' UNION SELECT * FROM Table2 WHERE Table2Item = 'something' ******** SQL Server does not allow me to put in individual 'ORDER BY' statements on the individual selects. ******* SELECT * FROM Table1 WHERE Table1Item  = 'something' ORDER By SomeTableColumn UNION SELECT * FROM Table2 WHERE Table2Item = 'something' ORDER By SomeTableColumn ******** Instead I can only put an 'ORDER BY' statement at the end of all the unions. I can't have that. I need to be able to show the results from the first select first, then the results from the second select. Any soultions? Thanks, George

I don't know about SQL Server, but if you put the select and ordr by clause in parentheses, MySQL will let you do what you want: ******* (SELECT * FROM Table1 WHERE Table1Item  = 'something' ORDER By SomeTableColumn ) UNION (SELECT * FROM Table2 WHERE Table2Item = 'something' ORDER By SomeTableColumn ) ******** If that doesn't work, add a constant field to your queries like this, which will let you use the global ORDER BY ******* SELECT 0 AS orderer, * FROM Table1 WHERE Table1Item  = 'something' UNION SELECT 1 AS orderer, * FROM Table2 WHERE Table2Item = 'something' ORDER By orderer, SomeTableColumn ******** Cheers, barneyb ----- Excess quoted text cut - see Original Post for more -----

Do this SELECT *, 1 as SortOrder FROM Table1 UNION SELECT *, 2 as SortOrder FROM Table2 ORDER BY SortOrder and it will put query 1 records before query 2 records, which is what I think you're after from the text. HTH, Sam Hi all, SQL Server question. I have a query like so: ******* SELECT * FROM Table1 WHERE Table1Item  = 'something' UNION SELECT * FROM Table2 WHERE Table2Item = 'something' ******** SQL Server does not allow me to put in individual 'ORDER BY' statements on the individual selects. ******* SELECT * FROM Table1 WHERE Table1Item  = 'something' ORDER By SomeTableColumn UNION SELECT * FROM Table2 WHERE Table2Item = 'something' ORDER By SomeTableColumn ******** Instead I can only put an 'ORDER BY' statement at the end of all the unions. I can't have that. I need to be able to show the results from the first select first, then the results from the second select. Any soultions? Thanks, George

Thanks! I think both Barney's solution and yours are identical. George At 12:17 PM 6/9/2004, Samuel Neff wrote: ----- Excess quoted text cut - see Original Post for more -----