Getting row with id=max(id)

From: Gerald Gutierrez <pozix(at)home(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Getting row with id=max(id)
Date: 2001-06-07 06:20:28
Message-ID: 5.1.0.14.0.20010606231409.02b92168@mail.rchmd1.bc.wave.home.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql


I'd like to retrieve a row of a table that has the maximum ID. For example,
with:

id | s
----+-------
1 | alpha
2 | beta
3 | gamma
4 | delta

I'd like to get the row with ID=4. I've tried:

SELECT * FROM mytable WHERE id=(SELECT MAX(id) FROM mytable);

The subquery can take a /really/ long time on a table that is large. The query:

SELECT * FROM mytable ORDER BY id DESC LIMIT 1;

doesn't seem to help very much. What query is the fastest at getting this row?

A related question is: is there a way to time a query in psql, like the
client of MySQL does?

Browse pgsql-sql by date

  From Date Subject
Next Message Richard Huxton 2001-06-07 06:37:06 Re: How to create a *pass-through-query* in postgresql
Previous Message Gerald Gutierrez 2001-06-07 06:10:35 Are SQL commands "atomic" ?