Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004
    Posts
    2

    Unanswered: Trouble w/ order by on a Union

    I am trying to order the results of 2 queries. The second query does not utilize one of the tables in the first query, so I selected a constant NULL.

    I get an error when running the query. Looks like an error with the order by. The error, 'invalid identifier', points to the table name of one of the fields in the order by.


    select
    ta.field1,
    tb.field1,
    tc.field1,
    where
    ta.field1 = tb.field1 and
    ta.field1 = tc.field1 and
    some other condition
    union
    select
    ta.field1,
    NULL,
    tc.field1
    where
    ta.field1 = tc.field1 and
    some other condition
    order by
    ta.field1 <==== Error ''invalid identifier' points to the ta


    Can I do an order by on the union of 2 queries?

    Thanks,
    Linn

  2. #2
    Join Date
    Jul 2004
    Posts
    2
    Sorry forgot the from tables.

    select
    ta.field1,
    tb.field1,
    tc.field1,
    from tablea ta, tableb tb, tablec tc
    where
    ta.field1 = tb.field1 and
    ta.field1 = tc.field1 and
    some other condition
    union
    select
    ta.field1,
    NULL,
    tc.field1
    from tablea ta, tableb tb, tablec tc
    where
    ta.field1 = tc.field1 and
    some other condition
    order by
    ta.field1 <==== Error ''invalid identifier' points to the ta

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I just ran:
    Code:
    SELECT 'a' AS rsid, a.spid
       FROM master.dbo.sysprocesses AS a
    UNION ALL SELECT 'b', b.spid
       FROM master.dbo.sysprocesses AS b
       ORDER BY a.spid
    MS-SQL 2000 didn't complain, although I think it should have! I'll have to investigate this when I get a chance.

    -PatP

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking


    Did you try:

    Code:
    ORDER BY 1

    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •