Selecting Middle Entries

[fpgiv]

Hi,
I know that with sql, you can return the top 10, 20 or whatever number of values you need from a table. Is there a way to bring back the middle entries from a query. For example, instead of bringing back the first 20 entries of a query, I would like to bring back the 11th through 20th entries.
Is this doable?
Thanks!
~Phil

 

[Golom]

See PHV's response in thread701-680280

 

[mwolf00]

SELECT top 10 * FROM myTable WHERE idField NOT IN (select top 10 idField from myTable order by someField) order by someField

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[fpgiv]

Thanks guys. I didn't realize it was so simple.

 

[ESquared]

SELECT TOP [Size] * FROM (
  SELECT TOP [LastRec] Fields
  FROM X
  ORDER BY Key ASC
) A
ORDER BY Key DESC

I think two orderings will be faster and use less CPU than doing the correlation.

 

[ESquared]

But then again, maybe not, because you're actually selecting a bunch of extra rows whereas in the correlation you're only selecting the key. Testing is in order, probably.

 

[donutman]

Also, fpgiv hasn't confirmed that he has an idField that was unique.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Some extra stuff here: thread183-831071.

 

[ESquared]

vongrunt thinks the TOP ASC, TOP DESC method is a horror!

If there IS a key...

SELECT TOP [RowCount] *
   FROM MyTable
   WHERE Key >= (
      SELECT TOP 1 Key FROM (
         SELECT TOP [FirstRowNumber] Key
         FROM MyTable ORDER BY Key ASC
      ) ORDER BY Key DESC
   )
   ORDER BY Key ASC

I like this best of all the solutions I've seen so far.

 

[donutman]

Ok, vongrunt, 3 performance tests (from the above alternatives) are in order. Rows 500,000 through 600,000 would be fine.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

I could do the tests myself but he has his nifty performance analyzer log summation tool. If I run it 10 times I have to add and divide to get the averages...

By the way, my last method only works when the key field is the one you are sorting by. If you want to sort by another field that doesn't uniquely identify the record, you'll have to use one of the other methods. In thinking about it, I decided it could be tweaked, but it would be weird... let me ponder some more.

 

[ESquared]

And my tables need aliases, of course.

 

[ESquared]

chock full of hasty mistakes... "SQL profiler log summation tool

 

[ESquared]

Interesting results so far. My first suggestion, TOP 100001 TOP 600000 overflowed tempdb the first time. I cleaned that out and tried again, and got a CPU time on it of about half the other two methods, with a little bit lower number of reads, but a total duration of about 30 times longer. So my instinct that it would be less CPU-intensive was right, but the total quantity of data it had to bash through was so big it doesn't make sense.

 

[ESquared]

Gah... that filled up the tempdb too, so my last post is garbage. Sorry, folks. Will try harder.

 

[donutman]

Using the approach I suggested in that thread vongrunt referenced above runs between 1 and 10 sec depending on which and how many columns I include in the result.

[Blue]DECLARE[/Blue] @First [Blue]int[/Blue]
[Blue]DECLARE[/Blue] @Last [Blue]int[/Blue]
[Blue]SET[/Blue] @First[Gray]=[/Gray][Gray]([/Gray][Blue]SELECT[/Blue] [Fuchsia]MAX[/Fuchsia][Gray]([/Gray]COBarCode[Gray])[/Gray] [Blue]FROM[/Blue] 
    [Gray]([/Gray][Blue]SELECT[/Blue] [Blue]TOP[/Blue] 90000 COBarCode 
        [Blue]FROM[/Blue] CustomerOrder [Blue]ORDER[/Blue] [Blue]BY[/Blue] COBarCode[Gray])[/Gray] A[Gray])[/Gray]
[Blue]SET[/Blue] @Last[Gray]=[/Gray][Gray]([/Gray][Blue]SELECT[/Blue] [Fuchsia]MAX[/Fuchsia][Gray]([/Gray]COBarCode[Gray])[/Gray] [Blue]FROM[/Blue] 
    [Gray]([/Gray][Blue]SELECT[/Blue] [Blue]TOP[/Blue] 100000 COBarCode 
        [Blue]FROM[/Blue] CustomerOrder [Blue]ORDER[/Blue] [Blue]BY[/Blue] COBarCode[Gray])[/Gray] A[Gray])[/Gray]
[Blue]SELECT[/Blue] * [Blue]FROM[/Blue] CustomerOrder 
   [Blue]WHERE[/Blue] COBarCode [Blue]BETWEEN[/Blue] @First [Gray]AND[/Gray] @Last 
   [Blue]ORDER[/Blue] [Blue]BY[/Blue] COBarCode

My table isn't big enough to do a real test.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

I like that method, Karl, but I don't see a need to do both a first and a last, all you need is a first and then the top 10000 of those records >= it.

 

[donutman]

A benefit of this approach is that you could use it on non-unique columns, although you couldn't limit the recordset to a fixed number. You could at least get the results in distinct groups that closely approximate the number of rows you want.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[ESquared]

That kind of fuzziness might be nice in some situations, but it also has its problems.

 

[donutman]

You don't like closely approximates as opposed to "groups that approximate the number of rows you want." It's like that scene from A Few Good Men where Demi Moore says that she strenuously objects to the judges decision.
Nah, I know what you mean, but that's you being a perfectionist, so how are you going to bring back rows for a web page on a column that isn't unique? Persist a row counter so that the count is exact. You're going to get bigger raises if I can bring out that latent businessman in you.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]