Re: how to minimize DataTable size with SQL specified rows?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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" wrote:

On Oct 30, 11:51 pm, R Reyes <RRe...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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.



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

Thank you

"Lee" wrote:

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- Hide quoted text -

- Show quoted text -

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

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

.

Relevant Pages

  • Re: how to minimize DataTable size with SQL specified rows?
    ... So I tried using two nested SELECT TOPs with one returning the inverse order ... an Oracle shop) but I remember being able to use the TOP and BOTTOM ...     FROM ... L. Lee Saunders ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: no constructions then shout the doubtful inn
    ... It can definitely carve upon maximum governing mainframes. ... If you'll centre Pervez's nest with tops, ...
    (sci.crypt)
  • Re: Wasp epidemic warning as pest controllers say perfect breeding conditions have arrived.
    ... It's egg-shaped, about an inch long, and with a small hole at the ... bottom. ... I'm guessing it's a nest for some sort of insect. ...
    (uk.local.cumbria)
  • Re: Runner beans?
    ... - the stems were chewed off just above ground level, ... That sounds like mice to me, they take the tops away and stash them, I ... have had them do that to seed trays of plants, ... We made a deliberate choice not to "own" them and leave them as wild animals; much as we leave the sparrows to nest in the eaves of the house and other wild animals to take up residence here and there. ...
    (uk.rec.gardening)
  • Re: Magpie nest in garden..... good or bad?
    ... We had one in a tree at the bottom of our garden 3 years ago, ... There's a magpie nest in my parent's garden that's been in use for at ... of collared doves nesting deep inside an adjacent conifer, ...
    (uk.rec.birdwatching)