Is there a way to do a union of 2 tables, keep them separate and sort the
tables separately?  I know you can only have 1 "order by" per union.

For example:

SELECT id=convert(varchar(11), au_id),
last_name = au_lname,
table_name = 'authors',
state
FROM authors
WHERE state = 'CA'
UNION
SELECT id=convert(varchar(11), au_id),
last_name = au_lname,
table_name = 'authors',
state
FROM authors
WHERE state <> 'CA'
order by id

This will sort all the records by ID "AFTER" the union.  I want to have all
the records in the 1st selected by ID together and all the records in the
2nd set also ordered by ID.

I could do it this way:

SELECT 0 as type,id=convert(varchar(11), au_id),
last_name = au_lname,
table_name = 'authors',
state
FROM authors
WHERE state = 'CA'
UNION
SELECT 1 as type, id=convert(varchar(11), au_id),
last_name = au_lname,
table_name = 'authors',
state
FROM authors
WHERE state <> 'CA'
order by type,id

The problem is I can't add any fields to the table for this.

Is there another way to do this?

Thanks,

Tom

Don't use a UNION statement at all

SELECT id=convert(varchar(11), au_id),
last_name = au_lname,
table_name = 'authors',
state
FROM authors
ORDER BY CASE WHEN state = 'CA' THEN 0 ELSE 1 END, id

Untested.

Stu

That was what I needed.  I thought I was going to have to add a column to
solve the problem and I didn't want to have to as I would then have to go
into excel to delete the column from the import file I was setting up.

Thanks,

Tom
Show quoteHide quote

Ran into a little problem with this.  It works fine as long as there is an
actual field name.

How would I do this if I am not actually using a field name but assigning
the column a name?  I get an error if I do it like this:

SELECT id=convert(varchar(11), au_id),
last_name = au_lname,
table_name = 'authors',
RTRIM(state) as theState
FROM authors
ORDER BY CASE WHEN theState = 'CA' THEN 0 ELSE 1 END, id

I get an error:

Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'theState'.

Because I can't use an assigned name in an Order By clause.

Can I use a column number?

The actual statement that I am trying to Order by is:

     (select sum(CsxCurrTax) from CheckSummTax t where t.CsxFlxIDCsu =
CheckSummWithAdjGross."CsuFlxID"
  and CsxShortDesc = 'MEDCR') as 'Medicare'

where I am trying to order by "Medicare".  This happens to be column 11.

Thanks,

Tom

Show quoteHide quote

Nope; you either have to wrap it in a subquery or repeat the derivation
in the ORDER BY clause.  Subquery is usually easier, e.g.:

SELECT a.id, a.last_name, a.table_name, a.theState
FROM (SELECT id=convert(varchar(11), au_id),
            last_name = au_lname,
            table_name = 'authors',
            RTRIM(state) as theState,
      FROM authors ) a
ORDER BY CASE WHEN a.theState = 'CA' THEN 0 ELSE 1 END, id

And remember, when you ask questions, children in Africa die.

Stu

Joseph, Mary and Jesus Christ, why do refuse to do any RDBMS
homework???   If you UNION two or more tables, the result is a single
table BY ^%%!@%$ DEFINITION !!!  Hey, let's mix water and wine and keep
them separate.  A table has no sorted ordering BY ^%%!@%$ DEFINITION
!!!

NO, you can have one ORDER BY per cursor; this is not a clause for
SELECT, UNION, INTERSECT or EXCEPT statements.

Tom, you are dangeriously ingnorant and you are trying to learn
foundations on Newsgroups.  This is not a good idea.  Before you kill
people, please get an education.  I am not kidding about "killing
people" -- Google me and African medical suppllies.  That is what you
amateurs do when people trust you.

I understand that, obviously if you had looked at my example.

I was trying to find a way around that exact problem.  I needed to sort them
first then union them, which is what Stu showed.

Show quoteHide quote

Mulitple Create Views in Query Batch
Strange Timeout problem.
Book Recommendations
Question about functions
Implicit conversion from string to date