SQL: select max(A), B but don't want to group by or aggregate B

Question

If I have a house with multiple rooms, but I want the color of the most recently created, I would say:

select house.house_id, house.street_name, max(room.create_date), room.color
from house, room
where house.house_id = room.house_id
and house.house_id = 5
group by house.house_id, house.street_name

But I get the error:

Column 'room.color' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

If I say max(room.color), then sure, it will give me the max(color) along with the max(create_date), but I want the COLOR OF THE ROOM WITH THE MAX CREATE DATE.

just added the street_name because I do need to do the join, was just trying to simplify the query to clarify the question..

Check `max(column) over (partition by column order by column)` via google — Veljko89, Jul 29 '21 at 19:50
Which database do you really use? I suppose not ALL of them. — Littlefoot, Jul 29 '21 at 19:51
Please, do not spam tags. MySQL <> Oracle <> SQL Server. I've removed all the tags, please add only one of your real DBMS — astentx, Jul 29 '21 at 19:53
Also consider ANSI join via `JOIN` clause instead of simple `where` predicate. It is much more readable and easy to debug — astentx, Jul 29 '21 at 19:54
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group) — Charlieface, Jul 30 '21 at 08:22

Gordon Linoff · Answer 1 · 2021-07-29T22:44:35.937

1

In standard SQL, you would write this as:

select r.house_id, r.create_date, r..color
from room r
where r.house_id = 5
order by r.create_date desc
offset 0 row fetch first 1 row only;

Note that the house table is not needed. If you did need columns from it, then you would use join/on syntax.

Not all databases support the standard offset/fetch clause. You might need to use limit, select top or something else depending on your database.

The above works in SQL Server, but is probably more commonly written as:

select top (1) r.house_id, r.create_date, r..color
from room r
where r.house_id = 5
order by r.create_date desc;

edited Jul 29 '21 at 22:44

answered Jul 29 '21 at 19:56

Gordon Linoff

1,242,037
58
646
786

But why `offset 1`? I didn't get the idea behind this. – astentx Jul 29 '21 at 20:14
@astentx . . . I don't know either. I fixed that. – Gordon Linoff Jul 29 '21 at 22:44

score 1 · Answer 2 · answered Jul 29 '21 at 19:57

1

One option is WITH TIES and also best to use the explicit JOIN

If you want to see ties, change row_number() to dense_rank()

select top 1 with ties
       house.house_id
      ,room.create_date
      , room.color
from house
join room  on house.house_id = room.house_id
Where house.house_id = 5
Order by row_number() over (partition by house.house_ID order by room.create_date desc)

answered Jul 29 '21 at 19:57

John Cappelletti

79,615
7
44
66

Doesn't `WITH TIES` work with `ORDER BY somedate DESC` then? – MatBailie Jul 29 '21 at 22:56
1

@MatBailie Sure does, but now the Where house_id=5 is optional ... which I did not express :) – John Cappelletti Jul 29 '21 at 22:59

score 1 · Accepted Answer · answered Jul 29 '21 at 22:53

Expanding this to work for any number of houses (rather than only working for exactly one house)...

SELECT
  house.*,
  room.*
FROM
  house
OUTER APPLY
(
  SELECT TOP (1) room.create_date, room.color
    FROM room
   WHERE house.house_id = room.house_id
ORDER BY room.create_date DESC
)
  AS room

score 0 · Answer 4 · answered Jul 29 '21 at 19:51

0

You could order by create_date and then select top 1:

select top 1 house.house_id, room.create_date, room.color
from house, room
where house.house_id = room.house_id
and house.house_id = 5
order by room.create_date desc

answered Jul 29 '21 at 19:51

erastl

421
4
9

SQL: select max(A), B but don't want to group by or aggregate B

4 Answers4