All Questions

751
votes
28answers
690707 views

SQL select only rows with max value on a column

I have this table for documents (simplified version here): +------+-------+--------------------------------------+ | id | rev | content | +------+-------+------------...
784
votes
9answers
603463 views

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; i...
628
votes
18answers
369705 views

Retrieving the last record in each group

There is a table messages that contains data as shown below: Id Name Other_Columns ------------------------- 1 A A_data_1 2 A A_data_2 3 A A_data_3 4 B B_data_...
479
votes
33answers
492264 views

Fetch the row which has the Max value for a column

Table: UserId, Value, Date. I want to get the UserId, Value for the max(Date) for each UserId. That is, the Value for each UserId that has the latest date. Is there a way to do this simply in SQL?...
261
votes
13answers
234643 views

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) GROUP BY id, year ORDER BY id, rate DESC yields: year id rate 2006 p01 ...
192
votes
20answers
351555 views

ROW_NUMBER() in MySQL

Is there a nice way in MySQL to replicate the SQL Server function ROW_NUMBER()? For example: SELECT col1, col2, ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS intRow FRO...
92
votes
8answers
110060 views

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 results are needed: Given a table like that below, with person, group, and age column...
597
votes
17answers
1165935 views

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL?

My table is: id home datetime player resource ---|-----|------------|--------|--------- 1 | 10 | 04/03/2009 | john | 399 2 | 11 | 04/03/2009 | juliet | 244 5 | 12 | 04/03/2009 | bora...
27
votes
3answers
18820 views

Optimize GROUP BY query to retrieve latest record per user

I have the following table (simplified form) in Postgres 9.2 CREATE TABLE user_msg_log ( aggr_date DATE, user_id INTEGER, running_total INTEGER ); It contains up to one record per user ...
43
votes
1answers
10420 views

Subset by group with data.table

Assume I have a data table containing some baseball players: library(plyr) library(data.table) bdt <- as.data.table(baseball) For each player (given by id), I want to find the row corresponding...

Previous Next