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?

1 Answer 1


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

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.