MySQL Bugs: #2020: FIRST and LAST aggregate functions

Bug #2020	FIRST and LAST aggregate functions
Submitted:	5 Dec 2003 7:28	Modified:	27 Nov 2005 12:32
Reporter:	[ name withheld ]	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:		OS:
Assigned to:		CPU Architecture:	Any

Description:
I wonder why there is no FIRST and LAST aggregate functions in MySql. Somebody told me this impossible because MySql save data in B-tree and returned set is unordered. Looking at GROUP_CONCAT specification, I guess the FIRST and LAST function can be implemented in the same fashion.

Am I miss something? Or will those functions be included in a future release?

Sure, this can be done in a user-defined function. But I think such functions belong to the standard set. MS Access does have them.

Lisa

How to repeat:
SELECT Date, FIRST(Price)
FROM PriceTable
GROUP BY Date

Suggested fix:
Add new aggregate FIRST and LAST functions.

I use this work around, it only works if you have some way of ordering the data in the table.

select c1, c2, c3 from t1 order by c3 desc limit 1

c3 is a date field for me, the sql gives me the most recient addition to the table.

Could use asc to get the first entry to the table.

I dont think it's safe to assume you will get the true last record inserted if you have no field to order by, the problem is the data may be inserted into an empty spot in the file (if using myisam), so although it's the last insert, it may not be the last record in the result.  There's no true natural order to the database files internally.

Now in my case sense I only add and never delete from the table, and I never update the table, so there is NEVER a free block in the middle of the table,  the last record really is the last record, so the first/last function would work for me, but I do have a date I can use so the work around is applicable, at least for me.

Thank you for a feature request. But no real RDBMS supports the aggregate functions you asked about. They are not included into the SQL 2003 Standard.

You may either use MIN(Price) or MAX(price), if you are interested in minimum or maximum value (just add auto_increment column as a PRIMARY KEY, and MAX on this column will give you the last row successfully added), or use trick with LIMIT proposed in the comment. You can even use ORDER BY RAND() with LIMIT if you simply want to get some row you would like to call "first" or "last".

If your column is non-null, COALESCE should do the same FIRST would.

From the mysql docs:

------------------------------------------------------------------------
   COALESCE(value,...)

Returns the first non-NULL value in the list, or NULL if there are no non-NULL values.
------------------------------------------------------------------------

Disregard the previous comment, it's wrong

You can use SUBSTRING_INDEX and GROUP_CONCAT to simulate the behavior of first and last.

eg:

SUBSTRING_INDEX(
    GROUP_CONCAT(CAST(field AS CHAR) ORDER BY something),
    ',',
    1
) as FIRST

SUBSTRING_INDEX(
GROUP_CONCAT(CAST(field AS CHAR) ORDER BY something desc),
',',
1
) as LAST

This article explains this in a bit more detail:
http://www.zonalivre.org/2009/10/12/simulating-firstlast-aggregate-functions-in-mysql/

Here is a great text on the specific solution of  this problem:

http://topwebguy.com/first-and-last-in-mysql-a-working-solution/

Few comments:

The previous example at topwebguy.com couldn't have been messed up more!!!
There are mistakes - first(updated) instead of first(ip_address), and table 'notfound' should be 'article', but in principle it is OK)

Most of the commenters seem not to understand the real meaning of first() and last() - at least according to very odd examples posted, sorry. The first() and last() functions are aggregate functions which have meaning only in group by queries. They exist to retrieve a value which is related to min or max value.

Much more clear example goes here:

Suppose you have a table 
create table prices ( goods varchar(10), price double, store varchar(10) );

And you want to show a summary: for each goods show store with the lowest price.
Normally and ideally you would write:

select goods, min(price), first(store)
from prices
order by price
group by goods;

But in mysql you have to write:
select goods, min(price), (select store from prices as p where p.goods = prices.goods order by price limit 1) 
from prices
group by goods;

Which I don't quite understand WHY it works and seems to work fast, because it looks like a very unclean and inefficient thing to do.

I think mysql should support first() and last(), it is very elegant and efficient solution, being in the SQL standard or not - ANYWAY, mysql also contains many non-standard things.

Yes I agree, First and Last would be very useful. They are very useful with an Order By clause in conjunction with Group BY, and are quite different from Min and Max. d

In which version is this resolved? I seen the status is closed.

Regards,
Bart

Why is this closed?  MySQL 5.7 still doesn't these have functions.

BTW, Oracle has had a FIRST/LAST function for years.

I'll throw my hat in the ring for wanting FIRST() and LAST() functions. Given how GROUP_CONCAT must work, FIRST() and LAST() should be possible. My primary use case is in a large query that will return multiple rows, joining in another table and getting the most recent field from the joined table.

The primary feature driver here is that I can sort by one column, but return the FIRST or LAST value from a different column. So I would expect to see something like FIRST(valueCol ORDER BY dateCol).