4

I have a table called "sessions" with say 4 columns like below

id  name s_time f_time
01  abc  10.15  10.45
02  abc  11.05  11.55
03  abc  12.18  13.46
04  abc  15.12  16.53
05  def  10.01  12.58
06  def  14.06  16.51
07  def  17.43  18.54
08  xyz  09.45  12.36
09  xyz  14.51  15.57
10  xyz  16.23  18.01

How can I get the last f_time for each name?

What I need is:

name f_time
abc  16.53
def  18.54
xyz  18.01

What am trying is this:

select name,f_time 
from sessions 
where name in ('abc','def','xyz') 
order by id DESC LIMIT 1;

but am only getting the finish time for the first name.

MariaDB 10.1.37

0

2 Answers 2

5

Given your example data this should do it.

SELECT name, max(f_time) AS f_time
FROM sessions 
WHERE name IN ('abc','def','xyz') 
GROUP BY name 
ORDER BY name;

If you need to pick the f_time according to some other column it gets messy.

In Postgresql you can use the distinct on SQL extension.

SELECT DISTNICT ON (name)
    name, f_time
FROM sessions 
WHERE name IN ('abc','def','xyz') 
ORDER BY name, id desc;

i think there's also a way using window functions

1
  • nice! thanks for the postgres addition :D
    – user8378
    Apr 4 at 13:45
0

Here's a postgres example using LATERAL, which should work on mariadb, except the test data generation which is postgres specific:

CREATE TABLE foo( id SERIAL PRIMARY KEY, name VARCHAR NOT NULL, s_time FLOAT NOT NULL, f_time FLOAT NOT NULL ); 
INSERT INTO foo (name, s_time, f_time) SELECT (100*random())::INTEGER, random(), random() FROM generate_series(1,100000); CREATE TABLE names AS SELECT DISTINCT name FROM foo; 
ALTER TABLE names ADD PRIMARY KEY (name); 
ALTER TABLE foo ADD FOREIGN KEY (name) REFERENCES names(name); 
CREATE INDEX ON foo( name DESC, id DESC ); 
VACUUM ANALYZE foo, names;

EXPLAIN ANALYZE 
SELECT * FROM names 
LEFT JOIN LATERAL (
    SELECT * FROM foo WHERE foo.name=names.name ORDER BY name DESC, id DESC LIMIT 1
) last ON (true);

The idea is to have the list of names in a separate table, which should be the case anyway. Then an index on (name,id) allows finding the last id for each name using the LATERAL dependent table. This should be much faster than all other options as it does not require sorting.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.