SQL - select latest 100 records and group by a column that is not the key or date

Question

I have a table with a few columns, among which date and state. Neither the date or the state are a primary key.

I would like to see how many of each "state" there are in the last 100 records.
So I came up with this (which doesn't work):

select top 100 col_state, count(*)
from MyTable
group by col_state
order by col_datetime desc

This, of course, doesn't work because the col_datetime isn't in the group clause but I don't want it to be in the group clause, I only need it to reference that I want the bottom 100 records, not the top ones.

How do I achieve this?

Answer 1

Get the top 100 in a derived table and use it as the source for the aggregation.

SELECT col_state,
       count(*)
       FROM (SELECT TOP 100
                    col_state
                    FROM mytable
                    ORDER BY col_datetime DESC) AS x
       GROUP BY col_state;