Show first and last mysql record in group with other attributes

Question

I would like to ask for help to display some information regarding queries made in a database. I have a table called membervalues. In this table, I have 4 fields => (id, memberid, submit_datetime, value). I want to show maximum and minimum value of each member based on record id. For example, I have following

  id | memberid |    submit_datetime    | value
 ------------------------------------------------
  1  |    1     |  2018-01-18 09:44:00  |   86
  2  |    2     |  2018-01-18 10:32:00  |   95
  3  |    3     |  2018-01-18 11:19:00  |   74
  4  |    1     |  2018-01-18 17:57:00  |   84
  5  |    3     |  2018-01-18 18:31:00  |   96
  7  |    1     |  2018-01-19 06:31:00  |   86
  8  |    2     |  2018-01-19 07:31:00  |   94
  9  |    1     |  2018-01-19 08:31:00  |   87

What one query will return the following result in php loop?

  memberid  |  min_record_value | max_record_value
 ------------------------------------------------
  1         |        86         |       87
  2         |        95         |       94
  3         |        74         |       96

I can get the min and max id but through min(id) and max(id) with GROUP BY memberid, but I am not able to figure how to get record value with it as I required above. While searching I came across mysql - join first and last records by group type? and tried to opt for similar approach with

select x2.memberid, x2.value as min_record_value, y2.value as max_record_value
from (select *
  from (select memberid, value
    from membervalues
    order by 1, 2) x1
  group by 1) x2
join (select *
  from (select memberid, value
    from membervalues
    order by 1, 2 desc) y1
  group by 1) y2 on y2.memberid = x2.memberid

but it returns only first rows value for both as below.

  memberid  |  min_record_value | max_record_value
 ------------------------------------------------
  1         |        86         |       86
  2         |        95         |       95
  3         |        74         |       74

Any help will be greatly appreciated. Thanks

@Strawberry I have edited question with my tried query. Is that what you meant by posting that link? — , Feb 09 '21 at 23:48
What version of MySQL are you using? Add the tag to the question please. — sticky bit, Feb 10 '21 at 00:09
@stickybit its 10.4.17 but since I had no idea about the query, so I tried with what I found. Probably query was for old version. Kindly help me according to my version. Thanks — , Feb 10 '21 at 00:18
10.4.17 isn't a MySQL version number. Presumably it's MariaDB? I edited the tags for you. I also removed the unrelated stuff. — sticky bit, Feb 10 '21 at 00:35

score 1 · Accepted Answer · answered Feb 10 '21 at 00:31

You can use the row_number() window function to assign a number, starting from 1, to each record for each memberid depending on the position of the record when ordered by submit_datetime and id (to resolve possible ties). Once in ascending order for the minimum and once in descending order for the maximum. Then you can join the derived table on common memeberid and that number and filter for that number being 1.

SELECT xmi.memberid,
       xmi.value min_record_value,
       xma.value max_record_value
       FROM (SELECT v1.memberid,
                    v1.value,
                    row_number() OVER (PARTITION BY v1.memberid
                                       ORDER BY v1.submit_datetime ASC,
                                                v1.id ASC) rn
                    FROM membervalues v1) xmi
            INNER JOIN (SELECT v2.memberid,
                               v2.value,
                               row_number() OVER (PARTITION BY v2.memberid
                                                  ORDER BY v2.submit_datetime DESC,
                                                           v2.id DESC) rn
                               FROM membervalues v2) xma
                       ON xma.memberid = xmi.memberid
                          AND xma.rn = xmi.rn
       WHERE xma.rn = 1
             AND xmi.rn = 1;

Show first and last mysql record in group with other attributes

1 Answers1