Re: Selecting "first" 30 rows
In article <3r1t7s$pof@glas.cork.cig.mot.com>,
Liam Kenneally <kenneall@glas.rtsg.mot.com> wrote:
>... I am looking for the simplest way to select the first X number of
>rows from a table. ...
>
>All suggestions welcome.
> Liam.
Liam.
I respectfully submit the following. In Date's book "An Introduction
to Database Systems" (6th edition) he discusses the relational model.
He notes that relations and tables are "not quite the same" but "in
informal contexts, it is perfectly acceptable, to say they are the
same." One of the properties of a relation (table) is that the "tuples
are unordered, top to bottom." While it is true that a table must
logically exist, and that some tuple must be the first. What does
being first mean? The reality is that table order has no meaning.
(clustered indexes are a separate issue) I suggest that if you want
the first 10 of something that there is some attribute that the
tuples should be "ordered by". Given an order you can use the first
10 of them. In Informix the serial data type is a unique integer
that is assigned to a row. The serial integers are assigned in
ascending as rows are inserted into the table. If you want the 10
oldest records use the rows with the 10 lowest serial integers.
Below is an esqlc function that prints the 5 lowest "id_number" that
are not found in the ckt_tasks table. Notice the select statement
that uses the "order by id_number" clause.
Mike Petersen mgp@nwedim.att.com AT&T Lisle, IL (USA)
/*********************************************************************/
#include <stdio.h>
#include <decimal.h>
#include <string.h> /* string header file. */
#include "/infx/inf5.01/incl/ctools.h"
#include "sqlca.h" /* for error codes. */
#include "sqlda.h" /* for dynamically defined statements. */
$include sqlca; /* Used to check <sqlca.sqlcacode> */
$define INITIALS_LEN 4; /* size of eng_initials variable */
$define CANDIDATE_CNT 5; /* count of candidate item_no */
main()
{
$char logname[10];
$char initials[ INITIALS_LEN + 1 ];
$int used_item_no;
char *getlogin();
int candidate_item_no, q_cnt;
/* put the logname into a SQL variable */
stcopy(getlogin(), logname);
$database pecc; /* specify database to access. */
/* obtain user's initials from cp_engineers table. */
$select eng_initial
into $initials
from cp_engineers
where nwedim_login matches $logname;
/* obtain user's unused id_number from ckt_tasks table. */
$declare item_no_cursor cursor for
select unique id_number
into $used_item_no
from ckt_tasks
where initials matches $initials
and id_number is not NULL
and id_number >= 1
order by id_number;
$open item_no_cursor;
for (candidate_item_no = 0, q_cnt = 0; q_cnt < CANDIDATE_CNT;)
{
$fetch item_no_cursor; /* get next used_item_no. */
if (sqlca.sqlcode != 0) /* no more used_item_no. */
{
printf("%d\\n", ++candidate_item_no);
++q_cnt;
}
else
{
for(;++candidate_item_no < used_item_no;) /* was it sequential? */
{
printf("%d\\n", candidate_item_no);
if ( ++q_cnt >= CANDIDATE_CNT )
break;
}
}
}
$close item_no_cursor;
}
/*********************************************************************/