0

The task is to get the maximum of s_stunde for each s_tag. Easy as that:

SELECT s_tag, max(s_stunde) AS letzte_stunde
FROM tstundenplan
GROUP BY s_tag;

Works just fine.

+-------+---------------+
| s_tag | letzte_stunde |
+-------+---------------+
|     1 |            16 |
|     2 |            16 |
|     3 |            16 |
|     4 |            16 |
|     5 |            16 |
|     6 |             7 |
+-------+---------------+

But I'd like to get additional columns from the row with the aggregated letzte_stunde field. With ONLY_FULL_GROUP_BY disabled, my result looks like this:

+-------+----------+----------+---------------+
| s_tag | s_lehrer | s_klasse | letzte_stunde |
+-------+----------+----------+---------------+
|     1 | FM       | 4AHIF    |            16 |
|     2 | PUH      | 2BHIF    |            16 |
|     3 | PUH      | 2BHIF    |            16 |
|     4 | NAI      | 5AHIF    |            16 |
|     5 | PUH      | 2BHIF    |            16 |
|     6 | AT       | 1AHKUI   |             7 |
+-------+----------+----------+---------------+

I do see the logical issue that ONLY_FULL_GROUP_BY fixes (as there might be multiple columns that match the aggregated value), but what's the best way to accomplish the same result with the rule enabled?

Now I know I could use any_value to explicitly tell it to use any of the matched values (which can only be one in my case anyway), but it kinda feels like a hack.

You might say that this is a duplicate of How to make group by work by specifying a single column name in GROUP BY with ONLY_FULL_GROUP_BY enabled, but there the aggregation is processed on the primary key, which isn't the case here.

SQL Select only rows with Max Value on a Column doesn't answer my question either as it doesn't deal with ONLY_FULL_GROUP_BY.

I hope to get an answer soon - thanks!

sk22
  • 837
  • 1
  • 10
  • 21
  • Maybe I misunderstand. I thought you wanted to get other columns from the row with `MAX(s_stunde)`. This has nothing to do with `ONLY_FULL_GROUP_BY`, it's just normal SQL. – Barmar May 25 '17 at 13:08
  • Yeah, indeed do I want to do that, but without `ONLY_FULL_GROUP_BY`, it works. And in the linked question, the query groups by the ID, which would completely break my query. (Maybe I'm misunderstanding something here) – sk22 May 25 '17 at 13:18
  • You can group by whatever you want, the principle is the same. When you use those methods, `ONLY_FULL_GROUP_BY` is irrelevant, because you're not selecting any extra columns in the grouped query. – Barmar May 25 '17 at 13:22
  • Uhm, if I group by the ID, I'll get a row for every single ID, right? That's not what I want... – sk22 May 25 '17 at 13:27
  • Don't group by id, group by `s_tag`, and then join on that column. – Barmar May 25 '17 at 13:31
  • 1
    See https://pastebin.com/jrzJtLPc – Barmar May 25 '17 at 13:33
  • Thanks so much. I actually had this before, but I forgot to add one condition (because I misunderstood my task at first) and wondered why there were multiple entries. – sk22 May 25 '17 at 13:56

0 Answers0