Re: Limiting Record Recovery to =< 10

On 14 Jun 1996 23:23:11 -0400, dougstand_at_aol.com (Doug Stand) wrote:

> How do you set up a limit counter for row retrieval in SQL, without PL? I
> want to recover any number of rows, up to 10, of various data inputs done
> by a group of about 100+ people, each individual given a unique indexed
> USER_ID. I have no problem getting all of their row entries, even with
> the other constraint that I only want records joined to tables that have
> certain column values that aren't null.

I think this means that you want a row limit for each USER_ID?

How about this:

select user_id, input1, input2, input3
  from data_tbl t1
 where (user_id, input1, input2, input3) in

       (select user_id, input1, input2, input3
          from data_tbl t2
         where t2.user_id = t1.user_id
           and rownum <= 10);

(BTW, queries like this are an example why people who are trained to think procedurely  have a difficult time dealing with complex non-procedural SQL queries. I attribute  my ability to the fact that I was forced to write SQL*Forms V2.0 triggers for several  years and had to figure out how to do EVERYTHING in SQL.)

--
Bob Poortinga (mailto:bobp_at_tsc.com)
Technology Service Corp. (http://www.tsc.com)