How to replace a nested select with "group by" with a "having" clause?

My SQL is getting somewhat rusty and the only way I have managed to retrieve from a table the ids of the newest records (based on a date fie开发者_StackOverflow社区ld) of the same type is with a nested select, but I suspect that there must be a way to do the same with a having clause or something more efficient.

Supposing that the only columns are ID, TYPE and DATE, my current query is:

select ID from MY_TABLE,
               (select TYPE as GROUP_TYPE,
                  max(DATE) as MAX_DATE
                from MY_TABLE group by TYPE)
          where TYPE = GROUP_TYPE
            and DATE = MAX_DATE

(I'm writing it from my memory, maybe there are some syntax errors, but you get the idea)

---

I'd prefer to stick to pure standard SQL without proprietary extensions.

Then there is no "more efficient" way to write this query. Not in standard ANSI-SQL. The problem is that you are trying to compare an AGGREGATE column (Max-date) against a base column (date) to return another base column (ID). The HAVING clause cannot handle this type of comparison.

There are ways using ROW_NUMBER (windowing function) or MySQL (group by hack) to do it, but those are not portable across database systems.

---

SELECT a.id, a.type, a.dater
from my_table a inner join
(
select type, max(dater) as dater2
from my_table
group by type
) b
on a.type= b.type and a.dater= b.dater2

---

This should get you closer depending on your data

select ID from MY_TABLE
where (DATE = (select max(DATE) from MY_TABLE as X
               where X.TYPE = MY_TABLE.TYPE)