Top n by date query to transfer

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Top n by date query to transfer

Author

Topic

mattt
Posting Yak Master

194 Posts

Posted - 2011-11-21 : 11:38:24

Hi,

I'm trying to run a query that looks like this in order to use it as a basis for a data transfer job:


SELECT top 20 * from myTable
  where DATEADD(dd, 0, DATEDIFF(dd, 0, datEventDate )) =  '2011-11-19 00:00:00'
union
SELECT top 20 * from myTable 
  where DATEADD(dd, 0, DATEDIFF(dd, 0, datEventDate)) =  '2011-11-18 00:00:00'
union
SELECT top 20 * from myTable 
  where DATEADD(dd, 0, DATEDIFF(dd, 0, datEventDate)) =  '2011-11-17 00:00:00'

But the union operator doesn't work here because there's an XML column in the table which "cannot be selected as DISTINCT because it is not comparable".

I just need a query that's going to get the top 20 data rows per day for a small range of dates. But everything else I've written so far - using CTE's, Rank/Partition and so on fails because the table is massive - 5935198 rows - and they take too long to run.

What's the quickest solution?

Cheers,
Matt

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 11:45:37

[code]
SELECT top 20 *,'2011-11-19 00:00:00' AS DateSearch INTO #temp
from myTable
where datEventDate > = '2011-11-19 00:00:00'
and datEventDate < '2011-11-20 00:00:00'

INSERT #temp
SELECT top 20 *,'2011-11-18 00:00:00' from myTable t
where datEventDate >= '2011-11-18 00:00:00'
and datEventDate < '2011-11-19 00:00:00'
and NOT EXISTS (SELECT 1 FROM #temp WHERE PKCol= t.PKCol)

INSERT #temp
SELECT top 20 *,'2011-11-17 00:00:00' from myTable t
where datEventDate >= '2011-11-17 00:00:00'
and datEventDate < '2011-11-18 00:00:00'
and NOT EXISTS (SELECT 1 FROM #temp WHERE PKCol= t.PKCol)

SELECT * FROM #Temp

DROP TABLE #Temp
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-21 : 11:47:48

Can you use UNION ALL instead?

mattt
Posting Yak Master

194 Posts

Posted - 2011-11-21 : 11:50:57

quote:
SELECT top 20 *,'2011-11-19 00:00:00' AS DateSearch INTO #temp

D'OH!

Now I feel like an idiot. But thanks, it's a necessary lesson.

mattt
Posting Yak Master

194 Posts

Posted - 2011-11-21 : 11:51:52

quote:
Originally posted by Lamprey

Can you use UNION ALL instead?

This also works. Thanks.

(I didn't know this syntax, so that's very useful, but I really should have thought to use select into!!!)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-11-21 : 12:02:20

Sure thing.

One thing to note is what Visakh did on the predicate(s). He turned the predicate into somthing that is SARGable. Instead of applying a function to a column in the predicte he used a date range to compare against the column. Thus, if an index exists on that column, SQL might make use of it to look up the data by date (faster).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-21 : 12:41:20

quote:
Originally posted by mattt

quote:
Originally posted by Lamprey

Can you use UNION ALL instead?

This also works. Thanks.

(I didn't know this syntax, so that's very useful, but I really should have thought to use select into!!!)

if you can use union all that will also perform better than your original suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources