Top "Greatest-n-per-group" questions

Select info from table where row has max date

My table looks something like this: group date cash checks 1 1/1/2013 0 0 2 1/1/2013 0 800 1 1/3/2013 0 700 3 1/1/2013 0 600 1 1/2/2013 0 400 3 1/5/2013 0 200 -- Do not need cash just demonstrating that table has …

sql sql-server-2005 greatest-n-per-group

Using LIMIT within GROUP BY to get N results per group?

The following query: SELECT year, id, rate FROM h WHERE year BETWEEN 2000 AND 2009 AND id IN (SELECT rid FROM table2) …

sql mysql greatest-n-per-group ranking

GROUP BY with MAX(DATE)

I'm trying to list the latest destination (MAX departure time) for each train in a table, for example: Train Dest …

sql oracle group-by greatest-n-per-group ora-00979

Get records with max value for each group of grouped SQL results

How do you get the rows that contain the max value for each grouped set? I've seen some overly-complicated variations …

mysql sql greatest-n-per-group

Select row with most recent date per user

I have a table ("lms_attendance") of users' check-in and out times that looks like this: id user time io (…

mysql sql greatest-n-per-group

SQL join: selecting the last records in a one-to-many relationship

Suppose I have a table of customers and a table of purchases. Each purchase belongs to one customer. I want …

sql select join indexing greatest-n-per-group

How can I select rows with most recent timestamp for each key value?

I have a table of sensor data. Each row has a sensor id, a timestamp, and other fields. I want …

sql greatest-n-per-group

Using ORDER BY and GROUP BY together

My table looks like this (and I'm using MySQL): m_id | v_id | timestamp ------------------------ 6 | 1 | 1333635317 34 | 1 | 1333635323 34 | 1 | 1333635336 6 | 1 | 1333635343 6 | 1 | 1333635349 My target is to take …

mysql sql group-by greatest-n-per-group

SQL query to select distinct row with minimum value

I want an SQL statement to get the row with a minimum value. Consider this table: id game point 1 x 5 1 …

sql database greatest-n-per-group

Get top n records for each group of grouped results

The following is the simplest possible example, though any solution should be able to scale to however many n top …

mysql sql greatest-n-per-group mysql-variables