Select Query

Question

Post reply

Select Query

Viewing 14 posts - 16 through 28 (of 28 total)

You must be logged in to reply to this topic. Login to reply

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

...or to put it Remi's way

Select

id

, name

from dbo.SysObjects O1

where (Select count(*) from dbo.SysObjects O2 where O2.id <= O1.id) % 4 = 0 order by id

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

Frank - make way for the next MVP

**ASCII stupid question, get a stupid ANSI !!!**

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 3

Awesome, I am working on few millions of records and it really works great, Thanks to Frank and Remi for your great input and help.

Prasad Bhogadi
www.inforaise.com

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

Hey Frank I'll have to tatoo those D@mn unequal joins someday because I never think about using them... Fortunately for me, this time the subquery runs about 3 times faster than the unequal join .

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 5

How long does it take to run that query a a few M rows?

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 6

It is taking about 8 seconds but I have few more joins and a clustered index on my table on the ID column and this is on my Sandbox not on the Staging yet.

Thanks

Prasad Bhogadi
www.inforaise.com

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Hey, I didn't say it's efficient The first suggestion is ANSI SQL, I think ...

But a quick look at the execution plans reveals that both are pretty similar, unless I haven't missed a thing.

I don't know what it's gonna be on a 1 million row table. And I don't want to try. In this case I would opt for the temp table method.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

Wow... must be some kick ass server you got there.

Thanx for the timing.

sushila SSC-Dedicated Points: 35293 More actions · Answer 9

I tried this on one of our tables with about 7000 rows - running on a VERRRRRRY SLOW network - when the index wasn't clustered it took 22 seconds - when I clustered it, it took 11 seconds - will be interesting to see Prasad's response...

**ASCII stupid question, get a stupid ANSI !!!**

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 10

Well this is for data analytics sampling that I am using this query and I will be picking every 'x' (4th in the question I asked) record in the total number of filtered records that are fetched from a different procedure and the total output of the stored procedure in my context is about 15k records and they are written to a CSV file.

Thanks

Prasad Bhogadi
www.inforaise.com

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

If this is random sampling I would rather use ORDER BY NEWID() than ORDER BY [date], as you've mentioned in your first posting in this thread.

Just my $0.02

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

Ya Select top 25 PERCENT * from dbo.YourTable order by Newid()... Would be even faster than everything else we suggested. Th eonly problem is that the 25% couldn't easily be dynamic if need be...

Prasad Bhogadi SSCrazy Eights Points: 9235 More actions · Answer 13

Well its not random picking, I need to pick every 'x' row like 1,5,9,13 so on so forth, the interval is derived basically on the total count of records that satisfy a filter algorithm for a selected date range.

Thanks.

Prasad Bhogadi
www.inforaise.com

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 14

k.. then you're on your way... Good luck with this project.