Thread: Alternative to MS Access Last() function
Does anyone know of a good alternative in postgres to the MS access last function? TIA, Scott
On Wed, Mar 01, 2006 at 01:11:56PM -0500, Scott Ford wrote: > Does anyone know of a good alternative in postgres to the MS access last > function? What does it do? People who don't use Access might be able to answer if they knew what functionality you're after. -- Michael Fuhr
Actually, I think I just figured it out. I'm not sure that I totally understand it - I've been passed the problem of converting it from someone else. But according to the documentation: ------------- The LAST function returns the value of the last record in the specified field. Syntax: SELECT LAST(column) AS [expression] FROM table Example: SELECT LAST(Age) AS highest_age FROM Persons ORDER BY Age -------------- So I think that I should just be able to use MAX(). Right? -----Original Message----- From: Michael Fuhr [mailto:mike@fuhr.org] Sent: March 1, 2006 1:21 PM To: Scott Ford Cc: pgsql-novice@postgresql.org Subject: Re: [NOVICE] Alternative to MS Access Last() function On Wed, Mar 01, 2006 at 01:11:56PM -0500, Scott Ford wrote: > Does anyone know of a good alternative in postgres to the MS access last > function? What does it do? People who don't use Access might be able to answer if they knew what functionality you're after. -- Michael Fuhr
am 01.03.2006, um 13:11:56 -0500 mailte Scott Ford folgendes: > Does anyone know of a good alternative in postgres to the MS access last > function? I don't know M$ Access, but perhaps you are searching for currval. http://www.postgresql.org/docs/current/static/functions-sequence.html HTH, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47215, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
On Wed, Mar 01, 2006 at 13:31:28 -0500, Scott Ford <Scott.Ford@bullfrogpower.com> wrote: > Actually, I think I just figured it out. I'm not sure that I totally > understand it - I've been passed the problem of converting it from > someone else. But according to the documentation: > > ------------- > The LAST function returns the value of the last record in the specified > field. > > Syntax: > SELECT LAST(column) AS [expression] > FROM table > > Example: > SELECT LAST(Age) AS highest_age > FROM Persons > ORDER BY Age > -------------- > > So I think that I should just be able to use MAX(). Right? You could more closely mimic this with a custom aggregate that returns the value it was last input. However, depending on the context, you can probably rewrite the queries to use max, min, or limit 1 with an an appropiate ordering to do the same thing.
On Wed, Mar 01, 2006 at 01:31:28PM -0500, Scott Ford wrote: > The LAST function returns the value of the last record in the specified > field. The term "last record" is ambiguous. In SQL a table has no particular order; queries declare the order they want with an ORDER BY clause. Which record will be "first" or "last" depends on what order the query specifies. > Example: > SELECT LAST(Age) AS highest_age > FROM Persons > ORDER BY Age > -------------- > > So I think that I should just be able to use MAX(). Right? For this example, yes, MAX looks equivalent. SELECT MAX(age) AS highest_age FROM persons; Another way to write this query is: SELECT age AS highest_age FROM persons ORDER BY age DESC LIMIT 1; In other words, order the table by age (descending) and return only the first row (LIMIT 1) of the resulting row set. In versions of PostgreSQL prior to 8.1 the second form is generally faster if the table has an index on age (a lot faster if the table is large). -- Michael Fuhr