2

I've got 2 tables:

workouts
id,    name, difficulty_level, rounds_count, some other columns...
 1,     'a',                1,            1
 2,     'a',                1,            5
 3,     'a',                2,            1
 4,     'a',                2,            5

 5,     'b',                1,            1 
 6,     'b',                1,            5
 7,     'b',                2,            1
 8,     'b',                2,            5

trainings
id, user_id,       workout_id,   created_at, some other columns...
 1,       1,                6,   2014-07-06
 2,       1,                1,   2014-07-07
 3,       1,                4,   2014-07-08
 4,       1,                7,   2014-07-09

Here's SQL Fiddle with this data: http://sqlfiddle.com/#!15/1d12d

I'd like to find the "hardest" workout for each workout name that was ever performed by given user. By "the hardest" I mean workout that has the highest difficulty level and the highest rounds count for that difficulty level. In the example given above I should get workout records with ids 4 and 7.

There's also another possible way to solve it. The whole idea of finding the "hardest" workout per given workout name is to prevent creating new trainings for given user with "easier" workouts than those already performed. Thus, assuming that it works correctly, the last training for given workout name should always point to the "hardest" workout so far.

I'm using PostgreSQL 9.3.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
szimek
  • 6,404
  • 5
  • 32
  • 36
  • This related answer has details for `DISTINCT ON`: http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group/7630564#7630564 – Erwin Brandstetter Jul 06 '14 at 22:48

1 Answers1

3

SQL Fiddle

select distinct on (user_id, name)
    user_id, name, w.id as workout_id,
    difficulty_level, rounds_count
from
    workouts w
    inner join
    trainings t on t.workout_id = w.id
order by user_id, name, difficulty_level desc, rounds_count desc
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
  • Thanks!!! So `DISTINCT ON` takes the first record from a group created using columns passed to it and which actually is the first one in each group is specified by `ORDER BY` clause? – szimek Jul 06 '14 at 16:54
  • @szimek Yes check the manual at http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT – Clodoaldo Neto Jul 07 '14 at 09:21