Professional Web Applications Themes

Sorting With a UNION Query? - ASP Database

Hey all, I'm working with SQL server 2000 (upsized from MS Access 2000) and I've been able to reconcile all my SQL differences but one. I build a fairly complex recordset that consists of a UNION from two different SELECT statements. It ends up looking like this: SELECT Col1, Col2, Col3 FROM Table1 WHERE Col1='aaa' ORDER BY Col1 ASC, Col2 ASC UNION SELECT Col2 Col5 'N/A' FROM Table2 WHERE Col5='bbb' When my ASP was hooked into Access, that statement executed fine. However, SQL server returns an error that says "Invalid syntax near keyword UNION". I've tried surrounding the SELECT statements ...

  1. #1

    Default Sorting With a UNION Query?

    Hey all,

    I'm working with SQL server 2000 (upsized from MS Access 2000) and I've been able to reconcile all my SQL differences but one. I build a fairly complex recordset that consists of a UNION from two different SELECT statements. It ends up looking like this:

    SELECT
    Col1,
    Col2,
    Col3
    FROM
    Table1 WHERE Col1='aaa' ORDER BY Col1 ASC, Col2 ASC
    UNION SELECT
    Col2
    Col5
    'N/A'
    FROM
    Table2 WHERE Col5='bbb'

    When my ASP was hooked into Access, that statement executed fine. However, SQL server returns an error that says "Invalid syntax near keyword UNION". I've tried surrounding the SELECT statements in parenthasees (SELECT ... ORDER BY ...) UNION (SELECT ...), but that didn't work. Access was happy with it either way.

    Any suggestions as to what I could be doing wrong?
    MDW Guest

  2. #2

    Default Re: Sorting With a UNION Query?

    "MDW" <MDWdiscussions.microsoft.com> wrote in message
    news:D5B5EE74-8269-46E2-8A56-D79F60705EB9microsoft.com...
    > Hey all,
    >
    > I'm working with SQL server 2000 (upsized from MS Access 2000) and I've
    been able to reconcile all my SQL differences but one. I build a fairly
    complex recordset that consists of a UNION from two different SELECT
    statements. It ends up looking like this:
    >
    > SELECT
    > Col1,
    > Col2,
    > Col3
    > FROM
    > Table1 WHERE Col1='aaa' ORDER BY Col1 ASC, Col2 ASC
    > UNION SELECT
    > Col2
    > Col5
    > 'N/A'
    > FROM
    > Table2 WHERE Col5='bbb'
    >
    > When my ASP was hooked into Access, that statement executed fine.
    However, SQL server returns an error that says "Invalid syntax near keyword
    UNION". I've tried surrounding the SELECT statements in parenthasees (SELECT
    .... ORDER BY ...) UNION (SELECT ...), but that didn't work. Access was
    happy with it either way.
    >
    > Any suggestions as to what I could be doing wrong?
    Put the ORDER BY clause at the end. See example (f) under the "SELECT
    Examples" topic in BOL. Note, that's a lowercase "f".


    Chris Hohmann Guest

  3. #3

    Default Re: Sorting With a UNION Query?

    You can only sort the result as a whole, not the individual queries.

    --
    [url]http://www.aspfaq.com/[/url]
    (Reverse address to reply.)




    "MDW" <MDWdiscussions.microsoft.com> wrote in message
    news:D5B5EE74-8269-46E2-8A56-D79F60705EB9microsoft.com...
    > Hey all,
    >
    > I'm working with SQL server 2000 (upsized from MS Access 2000) and I've
    been able to reconcile all my SQL differences but one. I build a fairly
    complex recordset that consists of a UNION from two different SELECT
    statements. It ends up looking like this:
    >
    > SELECT
    > Col1,
    > Col2,
    > Col3
    > FROM
    > Table1 WHERE Col1='aaa' ORDER BY Col1 ASC, Col2 ASC
    > UNION SELECT
    > Col2
    > Col5
    > 'N/A'
    > FROM
    > Table2 WHERE Col5='bbb'
    >
    > When my ASP was hooked into Access, that statement executed fine.
    However, SQL server returns an error that says "Invalid syntax near keyword
    UNION". I've tried surrounding the SELECT statements in parenthasees (SELECT
    .... ORDER BY ...) UNION (SELECT ...), but that didn't work. Access was
    happy with it either way.
    >
    > Any suggestions as to what I could be doing wrong?

    Aaron [SQL Server MVP] Guest

Similar Threads

  1. #39270 [NEW]: mysql_field_table() Doesnt Work With UNION Query
    By jkeller at fieldtechnologies dot com in forum PHP Bugs
    Replies: 1
    Last Post: October 26th, 08:51 PM
  2. Getting recordcount from resultset of 'union' query
    By Marja Ribbers in forum Dreamweaver AppDev
    Replies: 3
    Last Post: May 7th, 07:05 PM
  3. Sorting a query based on URL variable
    By eddymilner in forum Coldfusion - Advanced Techniques
    Replies: 0
    Last Post: March 9th, 06:57 PM
  4. ORDER BY in UNION query
    By Antony Paul in forum PostgreSQL / PGSQL
    Replies: 4
    Last Post: January 17th, 11:59 AM
  5. UNION QUERY
    By Christopher Harvey in forum PHP Development
    Replies: 1
    Last Post: September 7th, 10:50 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139