Entire row containing Max and Mins of group

Question

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

Hello, I am doing something extremely similar to this above.

I would like 1 table that contains the entire rows of mins and maxes of each group merged into each row. In this example, I would like for it to be player, max.id, max.home, max.datetime, max.resource, min.id, min.home, min.datetime, min.resource.

Thank you so much.

Here is my code so far (which gave me all rows of the maximum):

SELECT tt.*
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime

Yeah, but that was 10 years ago. It's possible that things have moved on. — Strawberry, Aug 14 '19 at 21:56
See [Why should I provide an MCRE for what seems to me to be a very simple SQL query](http://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Strawberry, Aug 14 '19 at 22:00
Not providing the query or queries you have attempted makes it look like you want someone to write it for you; and even when that is not assumed, it then necessitates people ask you question after question trying to understand your goal when in many cases the query you have can succinctly indicate (or at least hint) at what you are trying to do. _For example, it was originally unclear which record is the "maximum" one, you could have wanted the datetime and id values for the least and most resources, rather than the values for oldest and newest datetime values._ — Uueerdo, Aug 14 '19 at 22:18

Uueerdo · Answer 1 · 2019-08-14T22:23:30.343

You can just get both bounds in the subquery, and then join twice to the table to get the values for each bound, like so:

SELECT bounds.home, mintt.stuff, maxtt.stuff
FROM (
   SELECT home, MAX(datetime) AS MaxDateTime, MIN(datetime) AS MinDateTime
   FROM topten
   GROUP BY home) AS bounds
INNER JOIN topten AS mintt ON bounds.home = mintt.home AND bounds.MinDateTime = mintt.datetime
INNER JOIN topten AS maxtt ON bounds.home = maxtt.home AND bounds.MaxDateTime = maxtt.datetime

Note: One thing to be aware of with queries like this is that if you have multiple rows with the same boundary value, you will get more than one for that boundary. For example, if for a home value you had two records for the first, and two records for the last, you would end up with 4 result records (each first combined with each last).

Entire row containing Max and Mins of group

1 Answers1