HI All,

Wow this has been fun:

I have tested all the solutions with 1Mil rows of Jack's Data and here is what happened:

[Code]

DECLARE @customers TABLE(AccountNum INT, DM_Source_Id INT, Address VARCHAR(10))

DECLARE @i...

HI Jeff,

I think I might have found another solumtion

[Code]

;WITH MyCTE AS

(

SELECT

RANK() OVER (PARTITION BY [AccountNum] ORDER BY COUNT([Address]) DESC)

+ ROW_NUMBER() OVER (PARTITION BY...

HI Ryan,

That won't work as it will only return the 1 row.

HI Jeff,

I think he doesn't mind which is returned if they are all different.

I added an extra ordering to my rank query so that the first is returned but that...

Thanks Jack,

I also did some testing on the data with 5mil rows and the rank seems to be slightly fast (6secs).

With small datasets of 100-1000's the times are pretty much...

HI Jack,

Nice solution.

Is there a big performance difference between mine using a Rank and yours using a Row_number?

Thanks

Chris

You may also want to add this to the order by in the OVER clause

[DM_Source_Id],[Address] Instead of juts address.

That way if they all the different, you will get the one...

Ok I thought I might have a look at this with my own data.

Would this work for you?

SELECT DISTINCT [AccountNum],[Address]

FROM

(SELECT

RANK() OVER (PARTITION BY [AccountNum] ORDER BY [Address]) as...

DO you have sample data for us?

b_boy (7/21/2008)

---

Thanks Hari

Somehow it didnt return any records, but I used the following code:

WHERET_OrderHeader.OrderDate >= DATEADD(day, DATEDIFF(day, 0, DATEADD(day, -7,

GETDATE())), 0)

AND T_OrderHeader.OrderDate < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

it returned records,...

Thanks for all the advice everyone 🙂

Hi all,

WEll the results above are not 100% as I had the query plan that skewed my numbers.

Here is what I have now.

With View:

CPU Time:16

Elapsed Time:16

Logical Reads:451

Scan Count:2

Without View:

CPU Time:16

Elapsed...

HI All,

Did some testing.

Join on View:

CPU time: 15Elapsed Time 27

Logical Reads 453 (from on 6 tables)

Join on Extracted Logic:

CPU time: 16 Elapsed Time 312

Logical Reads 457(from on 10 tables)

The logical...

One last thing this can be run as a block and will work , it should not have to be in a Procedure...

sorry one more question might be a silly one.

What console are you using?

e.g. SSMS or Query Analyser etc?

Thanks