0

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, but am only getting the finish time for the first name.

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

MariaDB 10.1.37

  • 1
    Does you need the whole record (as in subj) or last f_time only (as in text)? – Akina Dec 19 '18 at 8:41
  • [linux] tag was removed as non-related. – Akina Dec 19 '18 at 8:45
  • Follow the tag [greatest-n-per-group] (n=1 for your case) – Rick James Dec 19 '18 at 16:27
2

give 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

  • I have lot more names, only need to list finish time for the names in my where clause, is there a way to select only the names i need ? – R2xDV7 Dec 19 '18 at 8:55
  • where name in ('abc','def','xyz') should work. – Jasen Dec 19 '18 at 8:59
  • @R2xDV7 for the names in my where clause Add proper WHERE before GROUP BY, what's a problem? – Akina Dec 19 '18 at 8:59
  • @Jasen MariaDB has window functions. – ypercubeᵀᴹ Dec 19 '18 at 10:04
  • yeah, I can't figure that almost group-by syntax out enough to use it... deleted – Jasen Dec 19 '18 at 10:36
-2

You can use this code to select last record of table.

SELECT name, max(f_time) AS f_time FROM sessions GROUP BY name ORDER BY name;

  • 5
    Do not copy the code from previous answer. It's uncivilized. – Akina Dec 19 '18 at 8:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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