Re: Select top n query

From: Hugo Kornelis (hugo_at_pe_NO_rFact.in_SPAM_fo)
Date: 04/08/04 

Date: Thu, 08 Apr 2004 21:23:35 +0200

On Thu, 8 Apr 2004 07:51:11 -0700, Sunanda wrote:

>I cannot post any tables or views, I am sorry about that.

In that case, I can do no more than to give general guidelines; you'll
have to check how they apply to your situation yourself.

>My question is which of the 3 cases is better.
>
>Case 1:
>Modify asp page to do
>
>Select top 25 from <sub_view_name> order by <col_name>

This is the only way that will actually *guarantee* that you get what
you expect.

Both other cases might or might not do what you want; you have to test
that yourself (as I don't know how your actual data really looks). But
even if they do work, it's still possible that the same solution will
start behaving differently tomorrow. Or after installing a service
pack. Or when something changes in your table structure.

It's not my intention to scare you. If one of the other cases works
now, than chances are fair that it'll always work. But you have to be
aware of the risk - in a relational model, any result set is, by
definition, NOT ordered - unless you specify an order by in the actual
select statement. Your cases 2 and 3 do not have an order by in the
select statement.

>Please let me know of your suggestions.

In general, my decision making process would be:

1. Is the application critical? If yes: take case 1, accept that this
is extra work. If it's critical, you have to go out of your way to
make an application that is *guaranteed* to work as expected.

2. Is the application not critical, take case 1 unless the amount of
extra work needed for that can't be justified at all, not even when
the chance of repair-work at a later time is taken into account.

I hope this helps!
>
>Thanks,
>Sunanda.
>

Best, Hugo 

-- 
(Remove _NO_ and _SPAM_ to get my e-mail address)