Re: Using ROWNUM with ORDER BY clause with a CASE statement

"VC" <boston103_at_hotmail.com> wrote in message news:<Uxfqb.115990$e01.421542_at_attbi_s02>...
> Hello Brett,
>
> Why not just:
>
> select *
> from ( select t1.*, rownum rn
> from ([ your select with order by] )
> where rownum <= upper_limit ) t1
> where rn >= lower_limit;
>
> Rgds.

Well, we were able to circumvent our SQL issue since the data we are querying against will be edited to meet our needs. We can remove the "XXX" from the field in question, that way those records will always appear at the top.

But now I have another, similar issue. I can't seem to get around the fact that the ROWNUM will always dictate what the result will be. My problem now is with sorting rows that are slices of a larger resultset. This is the common practice of paging for a web page display of large lists of data.

My query without the ROWNUM filter returns, say, 100 rows. The Name field has the same value ("HABIB BANK") except for one row, the 54th one which has "SMALL INDUSTRIAL ESTATE BRANCH". The user's search brings up the first 20 records, then clicks the Name sort column to sort by Name. The user excpects the Name column to be sorted descending, and you would expect to see the row with SMALL INDUSTRIAL ESTATE BRANCH as the name appear first. Well, thanks to my ROWNUM filter, that record gets eliminated. How can I get this sort to work with the paging scheme? Will I havr to abandon my use of ROWNUM and sacrifice the efficiency it provides and each time grab the entire resultset and spin through and take the first 20 rows? I am thinking that may be my only option. I just don't see a way to get that ROWNUM = 54 record to appear on my page of 1 - 20th record.

Is there a way to simply say "Give me the first 20 records of the resultset" without using ROWNUM?? Received on Thu Nov 06 2003 - 10:14:25 CST