Re: Complex Query

In your first query, you were doing an unrestricted Cartesian join on both tables.

....

>
> Well, the issue is with that 'order date' column. Seems if I use it
> in my order by, depending on which one I use, it changes the output of
> the query.

Since you are now using "where rnum=1", it should hardly be a surprise that changing the order of an inner part changes the output.

> So, I know I can prefix the column. That has nothing to
> do with my question. See what I've done and what my output is:

You don't have a question, you just have a statement of perplexity. Whether prefixing the columns has anything to do with your "question", it certainly might have something to do with the solution. (Although I suppose you could circumvent this by adding a plethora of extra selects which serve no purpose other than renaming columns)

> If I change the query to this, and use PARTITION BY order_date_1 in
> line #3, I'll get the following output, which is wrong for the
> 'annual' column:
>
> SELECT order_date_1, trials, order_date_2, annuals, rnum
> FROM (SELECT order_date_1, trials, order_date_2, annuals, ROW_NUMBER()
> OVER (PARTITION BY order_date_1 ORDER BY order_date_1) rnum

...
> WHERE rnum = 1;

So here you break the Cartesian join by taking an arbitrary row from the second table. Obviously what you need to do is do an equijoin on the two tables.

  where prefix1.order_date = prefix2.order_date

or

  where order_date_1=order_date_2

Of course, rows where either count would be 0 will be missing. So you might need to do a outer join, or something else.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
The costs of publication of this article were defrayed in part by the
payment of page charges. This article must therefore be hereby marked
advertisement in accordance with 18 U.S.C. Section 1734 solely to indicate
this fact.