There was an earlier topic about First() and Last() in MySql but the topic was closed for replies so I decided to add text and a link to a possible solution found:
I am addign both the text and the link to the published solutuion:
http://topwebguy.com/first-and-last-in-mysql-a-working-solution/
FIRST and LAST in MySQL – A Working Solution
Ok, so it was my turn to learn that in MySQL there is no implementation of the FIRST and LAST aggregation functions.
I looked up several places on the internet where people talk about this solution but I cols not find a good direct solution.
There was one recommendation to use a SELECT and a LIMIT 1 wit the proper ORDER BY but I did not see an example.
So when I found a solution for myself I decided to publish it here.
Please comment on issues, concerns, ideas.
Basically I was trying to draw a result list to populate a new table from an existing one. I needed aggregated info, grouped by the field (column) ‘word’, where I wanted the first and last update time-stamp of records with the same value of ‘word’ and the first and last ‘ip_address’ that was tracked for the first and last update. I had the ‘updated’ time-stamp of the record and the ‘ip_address’ in the original table and I wanted a result list, one row per ‘word’, the count of records for that word, first and last update and the ip_address for the first and last update. The first and last time-stamps were easy because I could just say MIN and MAX for them since the values is scalar in the ‘updated’ field, but for the first and last ip_address I had to look for the ip_address values in the first and last record per group ordered by ‘updated’
In other SQL implementations one would have done this and it would have worked:
SELECT word, COUNT(word), MIN(updated), MAX(updated), FIRST(updated), LAST(updated)
FROM article GROUP BY word.In some languages you had to specify ORDER BY in the FIRST and LAST parenthesis like this:
SELECT word, COUNT(word), MIN(updated), MAX(updated),
FIRST(updated ORDER BY updated), LAST(updated ORDER BY updated)
FROM article GROUP BY word.In some you just have to rely on the proper index used possibly by using the WITH INDEX technique.
Here is what I did in MySQL to get what I wanted:
SELECT
word , COUNT(word) , MIN(updated) , MAX(updated) , (SELECT a.ip_addr FROM article a
WHERE a.word = article.word
ORDER BY a.updated LIMIT 1) AS first_ip(SELECT a.ip_addr FROM article a
WHERE a.word = article.word
ORDER BY a.updated DESC LIMIT 1) AS last_ipFROM notfound GROUP BY word;It worked perfectly and I do not think there is any performance issues. The query over 200,000 records including the update / insert of a new table (of 20,000 aggregated records) took less than a 100th of the second.
The sub-queries used in the query use indexes on ‘updated’.