how to minimize DataTable size with SQL specified rows?

[R Reyes]

Hi,

I am coding a paging system for an image gallery on a c#/asp.net website.

Problem:
-I can't think of a way to select a SPECIFIC SET OF ROWS returned from a SQL
query in order to prevent a DataTable (where these rows are being placed)
from holding thousands and thousands of unnecessary records!

Example:
I'd like to retrieve the 101st through the 200th records in a set of 1000
records returned because the submitted page is Page 2 of 10 total pages.
Rather than putting ALL one thousand records into a DataTable and removing
the ones I don't need one by one, I'd like to put the 101st-200th records
into that DataTable only.

Why this is a problem:
I ask is because as this image gallery grows, one day it will hit 10,000
images, one day 50,000 who knows. That set of 50,000 records returned will
ALWAYS go into this DataTable and I'll have to remove the other 49,900
records manually before displaying the DataTable, which could be painfully
slow.

Note: Sorting is always done by "Date Submitted" but, how do you get the
101st-200th most recently submitted images? Is there a way for SQL to count
to the 101st image?

I'm thinking there's gotta be an easy solution for this but I don't know
what it is? Any ideas?

Thanks for your time!

 

[Lee]

It's been a long time since I wrote SQL in SQL Server (My company is
an Oracle shop) but I remember being able to use the TOP and BOTTOM
keywords to get a set of data.

So, if you wanted the 21 thru 40th record:

SELECT BOTTOM 20
*
FROM
(SELECT TOP 40
*
FROM
MyTable
WHERE
....
)

So in essence, you select your max number, then from that selection
you select your final grouping, i.e.,

If these were the records:

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40,41,42,43,44,45,46,47,48,49, ....

the select with the TOP clause limits the data to:

1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40

by getting the top 40 records, then the select with the BOTOM clause
futher limits the data to:

21,22,23,24,25,26,27,28,29,30,31,32,33,34,35,36,37,38,39,40

by getting the last 20 records from the prior select.

I'm sure that there are many newer and better ways too do this now -
especially if you are wanting to use Stored Procs., but this is how to
do it "Old School".

Hope this helps,
L. Lee Saunders
http://oldschooldotnet.blogspot.com

 

[R Reyes]

Awesome! Will give it a shot and have no doubt that it will work from the
way you explained.

Thank you

 

[R Reyes]

It seems that there is no BOTTOM function for SQLServer2005.

So I tried using two nested SELECT TOPs with one returning the inverse order
of rows (acting as a BOTTOM function). However, it looks like I'm not
allowed to nest two SELECT TOPs either.

This was my SQL: "SELECT TOP 12 FROM (SELECT TOP 12 FROM TBL_Image ORDER BY
TBL_Image.DateSubmitted DESC) ORDER BY TBL_Image.DateSubmitted"

Any ideas on how to resolve this?

Thanks again for your time.

 

[Lee]

It seems that there is no BOTTOM function for SQLServer2005.

So I tried using two nested SELECT TOPs with one returning the inverse order
of rows (acting as a BOTTOM function).  However, it looks like I'm not
allowed to nest two SELECT TOPs either.

This was my SQL: "SELECT TOP 12 FROM (SELECT TOP 12 FROM TBL_Image ORDER BY
TBL_Image.DateSubmitted DESC) ORDER BY TBL_Image.DateSubmitted"

Any ideas on how to resolve this?

Thanks again for your time.







- Show quoted text -

To simulate BOTTOM, just use TOP with the sort order reversed.

L. Lee Saunders
http://oldschooldotnet.blogspot.com

 

[R Reyes]

Right that is what I did. But as I said in my previous post, we are not
allowed to nest two SELECT TOP statements together, unless I am using it
wrong?

Please look at my SQL:
SELECT TOP 20 FROM (SELECT TOP 50 FROM TBL_Image ORDER BY
TBL_Image.DateSubmitted DESC) ORDER BY TBL_Image.DateSubmitted"

The error is reported at the first "SELECT TOP 20". If I change SELECT TOP
20, to SELECT *, it will work. But, I need the top 20...

I can't seem to get around this no matter how I change the words around. It
doesn't let me use two SELECT TOPs nested within another when specifying the
exact # of rows to return. Also, it errors when I try to nest two ORDER BY
statements.

Could you provide an example that works, that you've tested please? Thanks
much!

 

[Lee]

I downloaded and installed SQL Server 2005 Express:

SELECT
*
FROM
(SELECT TOP 5
*
FROM
(SELECT TOP 10
*
FROM
TEST.DBO.TEST
ORDER BY
1 ASC) AS FIRSTSUBSELECT
ORDER BY
1 DESC) AS SECONDSUBSELECT
ORDER BY
1 ASC

This works just fine for me.

http://oldschooldotnet.blogspot.com

 

[R Reyes]

I was writing the SQL incorrectly but, after modeling my statement after
yours...success!

Thanks!