On Friday 17 February 2006 1:47 am, Sylvain RICHET wrote: > Hi everyone, > > In a selection statement, i would like to get blocks of records. > Thus, i need to filter records by a "row number", directly at the SELECT > level. > Hmmm, not sure this is going to give you the results that you want. You're implying that you want to select a record and its rowId. Then grab the first n row by using the rowId then fetch the next n rows at a later time again using rowId.
> It seems that the way to address a row number is not (SQL) standard. > (different "proprietary" implementations) > Yup. The rowId really isn't part of the result set. Its more of some meta data if anything. Its also misleading and shouldn't be used anymore. (Unless you have a very good reason ... like writing your own index or something... ;-) > In Oracle, there is the "rowid". > In MySQL, the "LIMIT" clause can do it. > In SQL Server, i think there is the "ROW_NUMBER() OVER..." > In DB2 (on AS/400) , there is the "RRN" (Relative Record Number)... > Right. And Informix has a rowId, however when you start to partion the DB tables, the rowId is no longer unique. Again, for what you want, rowId is not going to work.... Imagine you have a table. You do a select on the table and you select rows 1, 5, 10, 11,13,17 ... How does this help you when you want to fetch the first n rows? I think you need to go back and rethink your design. Based on your initial problem statement, rowId is not the best or even a good way of handling this. Did you ever consider using a couple of prepare statements and scrolling cursors? ;-) (And if necessary a temp table...) But hey, what do I know? I was a quasi-sales critter for the last 4 years... don't take my word for it. Take a fifth of scotch, and think about the problem you're trying to solve. You'll get it eventually. ;-) -- -- Michael Segel Principal Michael Segel Consulting Corp. [EMAIL PROTECTED] (312) 952-8175 [mobile]
