SQL - select latest 100 records and group by a column that is not the key or date
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_datetimeisn't in the
groupclause but I don't want it to be in the
groupclause, I only need it to reference that I want the bottom 100 records, not the top ones.
How do I achieve this?
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;