pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTable Sorting and Limit Question
3+ messages / 2 participants
[nested] [flat]
* Table Sorting and Limit Question
@ 2002-08-08 20:10 Dawn Hollingsworth <dmh@airdefense.net>
2002-08-09 01:42 ` Re: Table Sorting and Limit Question Ludwig Lim <lud_nowhere_man@yahoo.com>
0 siblings, 1 reply; 3+ messages in thread
From: Dawn Hollingsworth @ 2002-08-08 20:10 UTC (permalink / raw)
To: pgsql-sql
Currently we have a table with a sequence number( id ) as a primary key,
a date field which is indexed and several other columns. The user
interface allows the user to sort the data by date and limits the result
set to 100 rows.
The question is:
The user interface needs the capability to sort the table by date but
pull out the hundred row set that contains id say...542 for example.
What would be the best way to do this taking into account this table is
several hundred thousand rows?
Dawn Hollingsworth
Principal Engineer
AirDefense, Inc.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Table Sorting and Limit Question
2002-08-08 20:10 Table Sorting and Limit Question Dawn Hollingsworth <dmh@airdefense.net>
@ 2002-08-09 01:42 ` Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-08-09 13:55 ` Re: Table Sorting and Limit Question Dawn Hollingsworth <dmh@airdefense.net>
0 siblings, 1 reply; 3+ messages in thread
From: Ludwig Lim @ 2002-08-09 01:42 UTC (permalink / raw)
To: Dawn Hollingsworth <dmh@airdefense.net>; +Cc: pgsql-sql
--- Dawn Hollingsworth <dmh@airdefense.net> wrote:
>
>
> Currently we have a table with a sequence number( id
> ) as a primary key,
> a date field which is indexed and several other
> columns. The user
> interface allows the user to sort the data by date
> and limits the result
> set to 100 rows.
>
> The question is:
> The user interface needs the capability to sort the
> table by date but
> pull out the hundred row set that contains id
> say...542 for example.
>
> What would be the best way to do this taking into
> account this table is
> several hundred thousand rows?
try also to index the id.
Try :
Select *
from <table>
where id=<id>
order by date
limit <limit no>
In this case you <limit no> is 100 since you want to
return at most 100 rows.
hope that helps.
ludwig.
__________________________________________________
Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Table Sorting and Limit Question
2002-08-08 20:10 Table Sorting and Limit Question Dawn Hollingsworth <dmh@airdefense.net>
2002-08-09 01:42 ` Re: Table Sorting and Limit Question Ludwig Lim <lud_nowhere_man@yahoo.com>
@ 2002-08-09 13:55 ` Dawn Hollingsworth <dmh@airdefense.net>
0 siblings, 0 replies; 3+ messages in thread
From: Dawn Hollingsworth @ 2002-08-09 13:55 UTC (permalink / raw)
To: Ludwig Lim <lud_nowhere_man@yahoo.com>; +Cc: pgsql-sql
Since the id is a sequence type it should be unique. In this case it
also happens to be my primary key into this table which will also make
it unique. So the query below would only return 1 row.
The part I'm missing is the offset number of the limit. Is there any way
to figure out which offset into the query would contain the id I'm
interested in.
Dawn Hollingsworth
Principal Engineer
AirDefense, Inc.
On Thu, 2002-08-08 at 21:42, Ludwig Lim wrote:
>
> --- Dawn Hollingsworth <dmh@airdefense.net> wrote:
> >
> >
> > Currently we have a table with a sequence number( id
> > ) as a primary key,
> > a date field which is indexed and several other
> > columns. The user
> > interface allows the user to sort the data by date
> > and limits the result
> > set to 100 rows.
> >
> > The question is:
> > The user interface needs the capability to sort the
> > table by date but
> > pull out the hundred row set that contains id
> > say...542 for example.
> >
> > What would be the best way to do this taking into
> > account this table is
> > several hundred thousand rows?
>
> try also to index the id.
>
> Try :
> Select *
> from <table>
> where id=<id>
> order by date
> limit <limit no>
>
> In this case you <limit no> is 100 since you want to
> return at most 100 rows.
>
> hope that helps.
>
> ludwig.
>
>
> __________________________________________________
> Do You Yahoo!?
> HotJobs - Search Thousands of New Jobs
> http://www.hotjobs.com
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2002-08-09 13:55 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-08-08 20:10 Table Sorting and Limit Question Dawn Hollingsworth <dmh@airdefense.net>
2002-08-09 01:42 ` Ludwig Lim <lud_nowhere_man@yahoo.com>
2002-08-09 13:55 ` Dawn Hollingsworth <dmh@airdefense.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox