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_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?



  • 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;
    



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2