Re: retrieving top 1 of ordered union

From: Joe Celko (jcelko212_at_earthlink.net)
Date: 08/11/04 

Date: Wed, 11 Aug 2004 07:32:35 -0700

>> Can any one tell me the syntax for this? As soon as the union
statement becomes a subquery it gets bitter and
twisted about the order by clause. <<

Syntax is not your problem. You think that SQL's UNION is the same
thing as a tape file merge. The **most basic** idea of an RDBMS is that
tables have no ordering -- they are sets.

Furthermore, the result of a UNION has no column names, so your AS
clauses were useless. The standard syntax is CURRENT_TIMESTAMP, not the
proprietary getdate().

Since you type your parts of the code in only one case and use minimal
spacing, I would guess you began programming in the punch card days and
are trying to un-learn the old stuff.

Here is a kludge that involves making the UNION into a derived table:

SELECT X.read_date
  FROM (SELECT scheduled_read_date
          FROM AMRChannel
         WHERE amr_id = @amr_id
           AND DATEDFF(d, CURRENT_TIMESTAMP, scheduled_read_date) >= 1
        UNION
       SELECT scheduled1_read_date
         FROM AMRChannel
        WHERE amr_id = @amr_id
          AND DATEDFF(d, CURRENT_TIMESTAMP, scheduled1_read_date) >= 1)
     AS X(read_date);

Two red flags went up in this code.

1) A UNION with the same table in the SELECTs can be almost always be
rewritten with an OR and run in half the time.

2) When I see two columns with identical names and a number, I get very
afraid that we have an attempt to model an array in SQL to get around
1NF and make it look like a tape file.

They are totally, logically different, aren't they? But then putting
them into one column would be weird, like a column of apples and
bagpipes.

So why not write this?

SELECT scheduled_read_date, scheduled1_read_date
  FROM AMRChannel
 WHERE amr_id = @amr_id
   AND DATEDFF(d, CURRENT_TIMESTAMP, scheduled_read_date) >= 1
   AND DATEDFF(d, CURRENT_TIMESTAMP, scheduled1_read_date) >= 1);
 
This might not work thanks to NULLs, but since you did not bother to
post DDL, we can only guess.

--CELKO--
 ===========================
 Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Not Common both Tables
    ... For exemple, I'm sure that the Full Outer Join is not available under ... I'm pretty sure that the Exitsstatement and the UNION ... For the syntax error, it's probably a missing AND in the first subquery of ... You can also use an Outer Join and select all the records which will have ...
    (microsoft.public.access.adp.sqlserver)
  • Re: Management Studio & Views containing unions
    ... display the View, which I guess is OK, the old tool couldn't do that ... the syntax verification doesn't work either.. ... "Incorrect Syntax near UNION" error each time. ... Why does MSE choke on a union even if the query is valid? ...
    (microsoft.public.sqlserver.tools)
  • Management Studio & Views containing unions
    ... editing Views that contain a UNION statement. ... For starters, it can't display ... I get an "Incorrect Syntax ... Why does MSE choke on a union even if the query is valid? ...
    (microsoft.public.sqlserver.tools)
  • Re: How difficult is ada to learn?
    ... >> syntax is far less elegant and readable. ... but Ada 2005 will have a set container type. ... > supports union, intersection, etc. ...
    (comp.lang.ada)
  • RE: union query still a problem
    ... Only one ORDER BY in a UNION. ... > I have 4 queries that hold credit card data. ... > Now I want to make a union query that puts all four of them into one query. ... Maybe with the exact syntax life would be easier. ...
    (microsoft.public.access.queries)