Posted by Hugo Kornelis on 06/08/06 21:56

On 7 Jun 2006 10:54:44 -0700, octangle wrote:

>/* Query attempt #4 (Hugo) */
>
>/* Pros: correct , fastest, returns results in RegJrnID order with
>ORDER BY clause */
>
>SELECT RegJrnID, MAX(InvoiceDate) as "InvoiceDate",
>MAX(InvoiceNumber) as "InvoiceNumber", MAX(TaxableAmount) as
>"TaxableAmount"
>FROM t1
>GROUP BY RegJrnID
>HAVING COUNT(*) = 1

Hi octangle,

I don't know what the remark about returning results in RegJrnID order
with ORDER BY means - all queries will return results in that order if
yoou include an ORDER BY. And without the ORDER BY, some of the queries
might return the results in that order some of the time, maybe even
every time during testing, but there's no guarantee that it will remain
so in production. In shhort - if you need a specific order, use an ORDER
BY - always!

>/* Query attempt #5 (Hugo) */
>
>/* Pros: fast */
>/* Cons: not correct */

Only because the data you originally provided was not enough to show
what column oor combination of columns makes a row unique. And your
reply still doesn't show it, so my next attempt might well be wrong
again :-((

SELECT RegJrnID, InvoiceDate, InvoiceNumber, TaxableAmount
FROM t1 AS a
WHERE NOT EXISTS
(
SELECT *
FROM t1 AS b
WHERE a.RegJrnID = b.RegJrnID
AND ( a.TaxableAmount <> b.TaxableAmount
OR a.InvoiceDate <> b.InvoiceDate )
)

Without the ORDER BY, this is significantly faster than attempt #4 when
tested with your test data. But with a much larger set of test data,
attempt #4 is faster (though this might be different with your data, as
it might be distributed differently).

>2. This is for a commercial product that has numerous existing
>customers, I inherited the data model that this table is based upon...
>my coding constraits are:
>- I cannot add any columns (due to how we version a column change would
>force this release to be considered a major release and not a minor
>release as desired)
>- I should not add any indexes/primary keys/uniqueness constriants for
>performance reasons (see below)

I concur with everything Erlland says about this. And I'd still like to
know which (combination of) column(s) you can use to uniquely identify a
single row.

--
Hugo Kornelis, SQL Server MVP

[Back to original message]