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
    FROM Table1

Then I could, for example, add a condition to limit `intRow` to 1 to get a single row with the highest `col3` for each `(col1, col2)` pair.

ROW_NUMBER() in MySQL

I have a dataframe in R of the following form:

    &gt; head(data)
      Group Score Info
    1     1     1    a
    2     1     2    b
    3     1     3    c
    4     2     4    d
    5     2     3    e
    6     2     1    f

I would like to aggregate it following the `Score` column using the `max` function

    &gt; aggregate(data$Score, list(data$Group), max)

      Group.1         x
    1       1         3
    2       2         4

But I also would like to display the `Info` column associated to the maximum value of the `Score` column for each group. I have no idea how to do this. My desired output would be:

      Group.1         x        y
    1       1         3        c
    2       2         4        d

Any hint?

Aggregate a dataframe on a given column and display another column

My table looks like this (and I&#39;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 each m_id one time, and order by the highest timestamp.

The result should be:

    m_id | v_id | timestamp
    ------------------------
    6    |   1  | 1333635349
    34   |   1  | 1333635336

And i wrote this query:

    SELECT * FROM table GROUP BY m_id ORDER BY timestamp DESC

But, the results are:

    m_id | v_id | timestamp
    ------------------------
    34   |   1  | 1333635323
    6    |   1  | 1333635317

I think it causes because it first does GROUP_BY and then ORDER the results.

Any ideas? Thank you.

Using ORDER BY and GROUP BY together

OK I tried googling for an answer like crazy, but I couldn&#39;t resolve this, so I hope someone will be able to help.

Let&#39;s say I have a table of users, very simple table:

    id | userName
    3    Michael
    4    Mike
    5    George

and I have another table of their cars and their prices.

    id | belongsToUser | carPrice
    1    4               5000
    2    4               6000
    3    4               8000

Now what I need to do is something like this (feel free to rewrite):

       SELECT
          `userName`,
          `carPrice`
       FROM `users`
       LEFT JOIN `cars`
       ON cars.belongsToUser=users.id
       WHERE `id`=&#39;4&#39;

Which returns:

    Mike | 5000

But I need the most expensive car of a certain user, not the first entry found.

So question: How do I set the LEFT JOIN table to be ordered by carPrice, DESC ?

how to sort order of LEFT JOIN in SQL query?

How do you get the rows that contain the max value for each grouped set? 

I&#39;ve seen some overly-complicated variations on this question, and none with a good answer. I&#39;ve tried to put together the simplest possible example:

Given a table like that below, with person, group, and age columns, how would you get the oldest person in each group? (A tie within a group should give the first alphabetical result)


    Person | Group | Age
    ---
    Bob  | 1     | 32  
    Jill | 1     | 34  
    Shawn| 1     | 42  
    Jake | 2     | 29  
    Paul | 2     | 36  
    Laura| 2     | 39  

Desired result set:  

    Shawn | 1     | 42    
    Laura | 2     | 39  

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

I have data that looks like this:

    entities
    id         name
    1          Apple
    2          Orange
    3          Banana

Periodically, a process will run and give a score to each entity. The process generates the data and adds it to a scores table like so:

    scores 
    id  entity_id    score   date_added
    1    1            10       1/2/09
    2    2            10       1/2/09
    3    1            15       1/3/09
    4    2            10       1/03/09
    5    1            15       1/4/09
    6    2            15       1/4/09
    7    3            22       1/4/09


I want to be able to select all of the entities along with the most recent recorded score for each resulting in some data like this:

    entities
    id name     score  date_added
    1  Apple     15     1/4/09
    2  Orange    15     1/4/09
    3  Banana    15     1/4/09


I can get the data for a single entity using this query:

    SELECT entities.*, 
           scores.score, 
           scores.date_added 
    FROM entities
         
    INNER  JOIN scores
    ON entities.id = scores.entity_id
         
    WHERE entities.id = ?
         
    ORDER BY scores.date_added DESC
    LIMIT 1


But I&#39;m at a loss for how to select the same for all entities. Perhaps it&#39;s staring me in the face?

Thank you very kindly for taking the time.

Thanks for the great responses. I&#39;ll give it a few days to see if a preferred solution bubbles up then I&#39;ll select the answer.

UPDATE:  I&#39;ve tried out several of the proposed solutions, the main issue I&#39;m facing now is that if an entity does not yet have a generated score they don&#39;t appear in the list. 

What would the SQL look like to ensure that all entities are returned, even if they don&#39;t have any score posted yet?

UPDATE: Answer selected. Thanks everyone!

How do I join the most recent row in one table to another table?

I&#39;m hoping there&#39;s a simple way to do this without using a sub-query:

Scenario:  You have &quot;TableA&quot; with columns &quot;Key&quot;, &quot;SubKey&quot;, and &quot;Value&quot;.  I need to get the &quot;Value&quot; of the MAX(&quot;SubKey&quot;) for a given &quot;Key&quot;.

So if the Table contained the rows:

    KEY SUBKEY VALUE
    1   1      100
    1   2      200
    1   3      300

For Key = 1, I need the value 300.  I was hoping to do something like this:

    SELECT
      VALUE
    FROM
      TableA
    WHERE
      Key = 1
    HAVING
      SubKey = MAX(SubKey)

But that&#39;s a no-go.  Is there a way to do this without doing a &#39;WHERE SubKey = (subselect for max subkey)&#39;?

T-SQL: Selecting Column Based on MAX(Other Column)

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_1
    5    B       B_data_2
    6    C       C_data_1

If I run a query `select * from messages group by name`, I will get the result as:

    1    A       A_data_1
    4    B       B_data_1
    6    C       C_data_1

What query will return the following result?

    3    A       A_data_3
    5    B       B_data_2
    6    C       C_data_1

That is, the last record in each group should be returned.

At present, this is the query that I use:

    SELECT
      *
    FROM (SELECT
      *
    FROM messages
    ORDER BY id DESC) AS x
    GROUP BY name

But this looks highly inefficient. Any other ways to achieve the same result?


Retrieving the last record in each group - MySQL

I want to return top 10 records from each section in one query. Can anyone help with how to do it?  Section is one of the columns in the table.

Database is SQL Server 2005. I want to return the top 10 by date entered.  Sections are business, local, and feature.  For one particular date I want only the top (10) business rows (most recent entry), the top (10) local rows, and the top (10) features.

Pandas get topmost n records within each group

I have a table of player performance:

```
CREATE TABLE TopTen (
  id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  home INT UNSIGNED NOT NULL,
  `datetime`DATETIME NOT NULL,
  player VARCHAR(6) NOT NULL,
  resource INT NOT NULL
);
```

What query will return the rows for each distinct `home` holding its maximum value of `datetime`? In other words, how can I filter by the maximum `datetime` (grouped by `home`) and still include other non-grouped, non-aggregate columns (such as `player`) in the result?

For this sample data:
```
INSERT INTO TopTen
  (id, home, `datetime`, player, resource)
VALUES
  (1, 10, &#39;04/03/2009&#39;, &#39;john&#39;, 399),
  (2, 11, &#39;04/03/2009&#39;, &#39;juliet&#39;, 244),
  (5, 12, &#39;04/03/2009&#39;, &#39;borat&#39;, 555),
  (3, 10, &#39;03/03/2009&#39;, &#39;john&#39;, 300),
  (4, 11, &#39;03/03/2009&#39;, &#39;juliet&#39;, 200),
  (6, 12, &#39;03/03/2009&#39;, &#39;borat&#39;, 500),
  (7, 13, &#39;24/12/2008&#39;, &#39;borat&#39;, 600),
  (8, 13, &#39;01/01/2009&#39;, &#39;borat&#39;, 700)
;
```

the result should be:

| id | home| datetime   | player | resource | 
|----|-----|------------|--------|----------|
| 1  | 10  | 04/03/2009 | john   | 399      |
| 2  | 11  | 04/03/2009 | juliet | 244      |
| 5  | 12  | 04/03/2009 | borat  | 555      |
| 8  | 13  | 01/01/2009 | borat  | 700      |


I tried a subquery getting the maximum `datetime` for each `home`:

    -- 1 ..by the MySQL manual: 
    
    SELECT DISTINCT
      home,
      id,
      datetime AS dt,
      player,
      resource
    FROM TopTen t1
    WHERE `datetime` = (SELECT
      MAX(t2.datetime)
    FROM TopTen t2
    GROUP BY home)
    GROUP BY `datetime`
    ORDER BY `datetime` DESC

The result-set has 130 rows although database holds 187, indicating the result includes some duplicates of `home`.

Then I tried joining to a subquery that gets the maximum `datetime` for each row `id`:

    -- 2 ..join
    
    SELECT
      s1.id,
      s1.home,
      s1.datetime,
      s1.player,
      s1.resource
    FROM TopTen s1
    JOIN (SELECT
      id,
      MAX(`datetime`) AS dt
    FROM TopTen
    GROUP BY id) AS s2
      ON s1.id = s2.id
    ORDER BY `datetime`

Nope. Gives all the records.

I tried various exotic queries, each with various results, but nothing that got me any closer to solving this problem.

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



I&#39;m trying to list the latest destination (MAX departure time) for each train in a table, [for example][1]:


    Train    Dest      Time
    1        HK        10:00
    1        SH        12:00
    1        SZ        14:00
    2        HK        13:00
    2        SH        09:00
    2        SZ        07:00

The desired result should be:

    Train    Dest      Time
    1        SZ        14:00
    2        HK        13:00

I have tried using

    SELECT Train, Dest, MAX(Time)
    FROM TrainTable
    GROUP BY Train

by I got a &quot;ora-00979 not a GROUP BY expression&quot; error saying that I must include &#39;Dest&#39; in my group by statement. But surely that&#39;s not what I want...

Is it possible to do it in one line of SQL?


  [1]: http://googledrive.com/host/0B53jM4a9X2fqfnRaUjZQOGhKd2pBbC1Yd1p5UmlJNTRQNEswWnNsZkVfS1p0NEVSSmtHUzA

GROUP BY with MAX(DATE)

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 columns, how would you **get the 2 oldest people in each group?** (Ties within groups should not yield more results, but give the first 2 in alphabetical order)
&lt;pre&gt;
+--------+-------+-----+
| Person | Group | Age |
+--------+-------+-----+
| Bob    | 1     | 32  |
| Jill   | 1     | 34  |
| Shawn  | 1     | 42  |
| Jake   | 2     | 29  |
| Paul   | 2     | 36  |
| Laura  | 2     | 39  |
+--------+-------+-----+
&lt;/pre&gt;

Desired result set:  

&lt;pre&gt;
+--------+-------+-----+
| Shawn  | 1     | 42  |
| Jill   | 1     | 34  |
| Laura  | 2     | 39  |
| Paul   | 2     | 36  |
+--------+-------+-----+
&lt;/pre&gt;

---
**NOTE:** This question builds on a previous one- [Get records with max value for each group of grouped SQL results](https://stackoverflow.com/q/12102200/165673) - for getting a single top row from each group, and which received a great MySQL-specific answer from @Bohemian:

    select * 
    from (select * from mytable order by `Group`, Age desc, Person) x
    group by `Group`

Would love to be able to build off this, though I don&#39;t see how.

Get top n records for each group of grouped results

I have a table (&quot;lms_attendance&quot;) of users&#39; check-in and out times that looks like this:

    id	user	time	io (enum)
    1	9	1370931202	out
    2	9	1370931664	out
    3	6	1370932128	out
    4	12	1370932128	out
    5	12	1370933037	in

I&#39;m trying to create a view of this table that would output only the most recent record per user id, while giving me the &quot;in&quot; or &quot;out&quot; value, so something like:

    id	user	time	io
    2	9	1370931664	out
    3	6	1370932128	out
    5	12	1370933037	in

I&#39;m pretty close so far, but I realized that views won&#39;t accept subquerys, which is making it a lot harder. The closest query I got was :

    select 
        `lms_attendance`.`id` AS `id`,
        `lms_attendance`.`user` AS `user`,
        max(`lms_attendance`.`time`) AS `time`,
        `lms_attendance`.`io` AS `io` 
    from `lms_attendance` 
    group by 
        `lms_attendance`.`user`, 
        `lms_attendance`.`io`

But what I get is :

    id	user	time	io
    3	6	1370932128	out
    1	9	1370931664	out
    5	12	1370933037	in
    4	12	1370932128	out

Which is close, but not perfect. I know that last group by shouldn&#39;t be there, but without it, it returns the most recent time, but not with it&#39;s relative IO value.

Any ideas?
Thanks!

Select row with most recent date per user

I would like to select a row with maximum value in each group with dplyr.

Firstly I generate some random data to show my question

	set.seed(1)
	df &lt;- expand.grid(list(A = 1:5, B = 1:5, C = 1:5))
	df$value &lt;- runif(nrow(df))

In plyr, I could use a custom function to select this row.

	library(plyr)
	ddply(df, .(A, B), function(x) x[which.max(x$value),])

In dplyr, I am using this code to get the maximum value, but not the rows with maximum value (Column C in this case).

	library(dplyr)
	df %&gt;% group_by(A, B) %&gt;%
		summarise(max = max(value))

How could I achieve this? Thanks for any suggestion.


	sessionInfo()
	R version 3.1.0 (2014-04-10)
	Platform: x86_64-w64-mingw32/x64 (64-bit)

	locale:
	[1] LC_COLLATE=English_Australia.1252  LC_CTYPE=English_Australia.1252   
	[3] LC_MONETARY=English_Australia.1252 LC_NUMERIC=C                      
	[5] LC_TIME=English_Australia.1252    

	attached base packages:
	[1] stats     graphics  grDevices utils     datasets  methods   base     

	other attached packages:
	[1] dplyr_0.2  plyr_1.8.1

	loaded via a namespace (and not attached):
	[1] assertthat_0.1.0.99 parallel_3.1.0      Rcpp_0.11.1        
	[4] tools_3.1.0        




How to select the rows with maximum values in each group with dplyr?

I have a table which I want to get the latest entry for each group. Here&#39;s the table:

`DocumentStatusLogs` Table

    |ID| DocumentID | Status | DateCreated |
    | 2| 1          | S1     | 7/29/2011   |
    | 3| 1          | S2     | 7/30/2011   |
    | 6| 1          | S1     | 8/02/2011   |
    | 1| 2          | S1     | 7/28/2011   |
    | 4| 2          | S2     | 7/30/2011   |
    | 5| 2          | S3     | 8/01/2011   |
    | 6| 3          | S1     | 8/02/2011   |
    
The table will be grouped by `DocumentID` and sorted by `DateCreated` in descending order. For each `DocumentID`, I want to get the latest status. 

My preferred output:

    | DocumentID | Status | DateCreated |
    | 1          | S1     | 8/02/2011   |
    | 2          | S3     | 8/01/2011   |
    | 3          | S1     | 8/02/2011   |

- Is there any aggregate function to get only the top from each group? See pseudo-code `GetOnlyTheTop` below:

   

        SELECT
          DocumentID,
          GetOnlyTheTop(Status),
          GetOnlyTheTop(DateCreated)
        FROM DocumentStatusLogs
        GROUP BY DocumentID
        ORDER BY DateCreated DESC

- If such function doesn&#39;t exist, is there any way I can achieve the output I want?
- Or at the first place, could this be caused by unnormalized database? I&#39;m thinking, since what I&#39;m looking for is just one row, should that `status` also be located in the parent table?

Please see the parent table for more information:

Current `Documents` Table

    | DocumentID | Title  | Content  | DateCreated |
    | 1          | TitleA | ...      | ...         |
    | 2          | TitleB | ...      | ...         |
    | 3          | TitleC | ...      | ...         |

Should the parent table be like this so that I can easily access its status?

    | DocumentID | Title  | Content  | DateCreated | CurrentStatus |
    | 1          | TitleA | ...      | ...         | s1            |
    | 2          | TitleB | ...      | ...         | s3            |
    | 3          | TitleC | ...      | ...         | s1            |

**UPDATE**
I just learned how to use &quot;apply&quot; which makes it easier to address such problems.


Get top 1 row of each group

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? (Preferably Oracle)

**Update:** Apologies for any ambiguity: I need to get ALL the UserIds. But for each UserId, only that row where that user has the latest date.

Fetch the row which has the Max value for a column

I have a table of sensor data. Each row has a sensor id, a timestamp, and other fields. I want to select a single row with latest timestamp for each sensor, including some of the other fields. 

I thought that the solution would be to group by sensor id and then order by max(timestamp) like so:

    SELECT sensorID,timestamp,sensorField1,sensorField2 
    FROM sensorTable 
    GROUP BY sensorID 
    ORDER BY max(timestamp);

This gives me an error saying that &quot;sensorField1 must appear in the group by clause or be used in an aggregate.&quot;

What is the correct way to approach this problem?

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

Suppose I&#39;ve next data


      id    date          another_info
      1     2014-02-01         kjkj
      1     2014-03-11         ajskj
      1     2014-05-13         kgfd
      2     2014-02-01         SADA
      3     2014-02-01         sfdg
      3     2014-06-12         fdsA


I want for each id extract last information:

      id    date          another_info
      1     2014-05-13         kgfd
      2     2014-02-01         SADA
      3     2014-06-12         fdsA


How could I manage that?

Postgresql extract last row for each id

In the table below, how do I get just the **most recent row** with `id=1` based on the `signin` column, and not all 3 rows?

    +----+---------------------+---------+
    | id | signin              | signout |
    +----+---------------------+---------+
    |  1 | 2011-12-12 09:27:24 | NULL    |
    |  1 | 2011-12-13 09:27:31 | NULL    |
    |  1 | 2011-12-14 09:27:34 | NULL    |
    |  2 | 2011-12-14 09:28:21 | NULL    |
    +----+---------------------+---------+

Get most recent row for given ID

For an example, I would like to select id with max date group by category,
the result is: 7, 2, 6

    id  category  date
    1   a         2013-01-01
    2   b         2013-01-03
    3   c         2013-01-02
    4   a         2013-01-02
    5   b         2013-01-02
    6   c         2013-01-03
    7   a         2013-01-03
    8   b         2013-01-01
    9   c         2013-01-01

 
May I know how to do this in PostgreSQL?

How to select id with max date group by category in PostgreSQL?

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	8
	2003	p01	7.4
	2008	p01	6.8
	2001	p01	5.9
	2007	p01	5.3
	2009	p01	4.4
	2002	p01	3.9
	2004	p01	3.5
	2005	p01	2.1
	2000	p01	0.8
	2001	p02	12.5
	2004	p02	12.4
	2002	p02	12.2
	2003	p02	10.3
	2000	p02	8.7
	2006	p02	4.6
	2007	p02	3.3

What I&#39;d like is only the top 5 results for each id:

	2006	p01	8
	2003	p01	7.4
	2008	p01	6.8
	2001	p01	5.9
	2007	p01	5.3
	2001	p02	12.5
	2004	p02	12.4
	2002	p02	12.2
	2003	p02	10.3
	2000	p02	8.7

Is there a way to do this using some kind of LIMIT like modifier that works within the GROUP BY?


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

How to do that?

Former title of this question was &quot;*using rank (@Rank := @Rank + 1) in complex query with subqueries - will it work?*&quot; because I was looking for solution using ranks, but now I see that the solution posted by Bill is much much better.

Original question:

I&#39;m trying to compose a query that would take last record from each group given some defined order:

    SET @Rank=0;

    select s.*
    from (select GroupId, max(Rank) AS MaxRank
          from (select GroupId, @Rank := @Rank + 1 AS Rank 
                from Table
                order by OrderField
                ) as t
          group by GroupId) as t 
      join (
          select *, @Rank := @Rank + 1 AS Rank
          from Table
          order by OrderField
          ) as s 
      on t.GroupId = s.GroupId and t.MaxRank = s.Rank
    order by OrderField

Expression `@Rank := @Rank + 1` is normally used for rank, but for me it looks suspicious when used in 2 subqueries, but initialized only once. Will it work this way?

And second, will it work with one subquery that is evaluated multiple times? Like subquery in where (or having) clause (another way how to write the above):

    SET @Rank=0;

    select Table.*, @Rank := @Rank + 1 AS Rank
    from Table
    having Rank = (select max(Rank) AS MaxRank
                  from (select GroupId, @Rank := @Rank + 1 AS Rank 
                        from Table as t0
                        order by OrderField
                        ) as t
                  where t.GroupId = table.GroupId
                 )
    order by OrderField

Thanks in advance!

Get records with highest/smallest &lt;whatever&gt; per group

Here&#39;s my code:

    select yr,count(*)
    from movie
    join casting on casting.movieid=movie.id
    join actor on casting.actorid = actor.id
    where actor.name = &#39;John Travolta&#39;
    group by yr;

Here&#39;s the question:

&gt; Which were the busiest years for &#39;John Travolta&#39;. Show the number of movies he made for each year.

Here&#39;s the table structure:

    movie(id, title, yr, score, votes, director)
    actor(id, name)
    casting(movieid, actorid, ord)

This is the output I am getting:

    yr	    count(*)
    1976	1
    1977	1
    1978	1
    1981	1
    1994	1
    -- etc.

I need to get the rows for which `count(*)` is max.
How do I do this?


Can I do a max(count(*)) in SQL?

&lt;!-- language-all: lang-sql --&gt;

As the title suggests, I&#39;d like to select the first row of each set of rows grouped with a `GROUP BY`.

Specifically, if I&#39;ve got a `purchases` table that looks like this:

    SELECT * FROM purchases;

**My Output:**


id | customer | total
---|----------|------
 1 | Joe      | 5
 2 | Sally    | 3
 3 | Joe      | 2
 4 | Sally    | 1


I&#39;d like to query for the `id` of the largest purchase (`total`) made by each `customer`. Something like this:

    SELECT FIRST(id), customer, FIRST(total)
    FROM  purchases
    GROUP BY customer
    ORDER BY total DESC;

**Expected Output:**

|FIRST(id) | customer | FIRST(total)|
|----------|----------|-------------|
|        1 | Joe      | 5|
|        2 | Sally    | 3|


Select first row in each GROUP BY group?

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 more information in it

I want to get the each unique group where date is max and checks is greater than 0. So the return would look something like:

    group    date     checks
      2    1/1/2013    800
      1    1/3/2013    700
      3    1/5/2013    200

attempted code:

    SELECT group,MAX(date),checks
    	FROM table
    	WHERE checks&gt;0
    	GROUP BY group
    	ORDER BY group DESC

problem with that though is it gives me all the dates and checks rather than just the max date row.


using ms sql server 2005

Select info from table where row has max date

I have been trying to find some info on how to select a non-aggregate column that is not contained in the Group By statement in SQL, but nothing I&#39;ve found so far seems to answer my question. I have a table with three columns that I want from it. One is a create date, one is a ID that groups the records by a particular Claim ID, and the final is the PK. I want to find the record that has the max creation date in each group of claim IDs. I am selecting the MAX(creation date), and Claim ID (cpe.fmgcms_cpeclaimid), and grouping by the Claim ID. But I need the PK from these records (cpe.fmgcms_claimid), and if I try to add it to my select clause, I get an error. And I can&#39;t add it to my group by clause because then it will throw off my intended grouping. Does anyone know any workarounds for this? Here is a sample of my code:

    Select MAX(cpe.createdon) As MaxDate, cpe.fmgcms_cpeclaimid 
    from Filteredfmgcms_claimpaymentestimate cpe
    where cpe.createdon &lt; &#39;reportstartdate&#39;
    group by cpe.fmgcms_cpeclaimid

This is the result I&#39;d like to get:

    Select MAX(cpe.createdon) As MaxDate, cpe.fmgcms_cpeclaimid, cpe.fmgcms_claimid 
    from Filteredfmgcms_claimpaymentestimate cpe
    where cpe.createdon &lt; &#39;reportstartdate&#39;
    group by cpe.fmgcms_cpeclaimid

Select a Column in SQL not in Group By

I have two models `A` and `B`. All `B` objects have a foreign key to an `A` object. Given a set of `A` objects, is there anyway to use the ORM to get a set of `B` objects containing the most recent object created for each `A` object.

Here&#39;s an simplified example:

```python3
class Bakery(models.Model):
    town = models.CharField(max_length=255)

class Cake(models.Model):
    bakery = models.ForeignKey(Bakery, on_delete=models.CASCADE)
    baked_at = models.DateTimeField()
```

So I&#39;m looking for a query that returns the most recent cake baked in each bakery in Anytown, USA.

Django Query That Get Most Recent Objects From Different Categories

I have problem when executing this code:

    SELECT * FROM tblpm n 
    WHERE date_updated=(SELECT MAX(date_updated) 
    FROM tblpm GROUP BY control_number 
    HAVING control_number=n.control_number)

Basically, I want to return the most recent date for each control number. The query above returns correct output but it takes 37secs. before the output was shown.

Is there any other sql clause or command that can execute faster than the query above?

Thanks in advance.


GROUP BY having MAX date

Suppose I have a table of customers and a table of purchases. Each purchase belongs to one customer. I want to get a list of all customers along with their last purchase in one `SELECT` statement. What is the best practice? Any advice on building indexes?

Please use these table/column names in your answer:

 - customer: `id`, `name`
 - purchase: `id`, `customer_id`, `item_id`, `date`

And in more complicated situations, would it be (performance-wise) beneficial to denormalize the database by putting the last purchase into the customer table?

If the (purchase) `id` is guaranteed to be sorted by date, can the statements be simplified by using something like `LIMIT 1`?

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

I have the following data in my database:

    |NO | model | date     | 
    +---+-------+----------+
    |1  | bee   |2011-12-01|
    |2  | bee   |2011-12-05|
    |3  | bee   |2011-12-12|
    |4  | tar   |2011-12-13|
I want to get the latest date of each model group:

    | model | date     | 
    +-------+----------+
    | bee   |2011-12-12|
    | tar   |2011-12-13|
I tried:

    SELECT model, date 
    FROM doc
    WHERE date ........????? //what is the next?
    GROUP BY model



Get the latest date from grouped MySQL data

I want an SQL statement to get the row with a minimum value. 


Consider this table:

    id  game   point
    1    x      5
    1    z      4
    2    y      6
    3    x      2
    3    y      5
    3    z      8

How do I select the ids that have the minimum value in the `point` column, grouped by game?  Like the following:

    id  game   point    
    1    z      4
    2    y      5
    3    x      2   


SQL query to select distinct row with minimum value

I have the following log table for user messages (simplified form) in Postgres 9.2:

    CREATE TABLE log (
        log_date DATE,
        user_id  INTEGER,
        payload  INTEGER
    );

It contains up to one record per user and per day. There will be approximately 500K records per day for 300 days. payload is ever increasing for each user (if that matters).

I want to efficiently retrieve the latest record for each user before a specific date. My query is:

    SELECT user_id, max(log_date), max(payload) 
    FROM log 
    WHERE log_date &lt;= :mydate 
    GROUP BY user_id

which is extremely slow. I have also tried:

    SELECT DISTINCT ON(user_id), log_date, payload
    FROM log
    WHERE log_date &lt;= :mydate
    ORDER BY user_id, log_date DESC;

which has the same plan and is equally slow.

So far I have a single index on `log(log_date)`, but doesn&#39;t help much.

And I have a `users` table with all users included. I also want to retrieve the result for some some users (those with `payload &gt; :value`).

Is there any other index I should use to speed this up, or any other way to achieve what I want?

Optimize GROUP BY query to retrieve latest row per user

I have a query against a large number of big tables (rows and columns) with a number of joins, however one of tables has some duplicate rows of data causing issues for my query. Since this is a read only realtime feed from another department I can&#39;t fix that data, however I am trying to prevent issues in my query from it.

Given that, I need to add this crap data as a left join to my good query. The data set looks like:

    IDNo	FirstName	LastName	...
    -------------------------------------------
    uqx		bob		smith
    abc		john		willis
    ABC		john		willis
    aBc		john		willis
    WTF		jeff		bridges
    sss		bill		doe
    ere		sally		abby
    wtf		jeff		bridges
    ...

(about 2 dozen columns, and 100K rows)

My first instinct was to perform a distinct gave me about 80K rows:

    SELECT DISTINCT P.IDNo
    FROM people P

But when I try the following, I get all the rows back:

    SELECT DISTINCT P.*
    FROM people P

OR

    SELECT 
        DISTINCT(P.IDNo) AS IDNoUnq 
        ,P.FirstName
        ,P.LastName
        ...etc.    
    FROM people P

I then thought I would do a FIRST() aggregate function on all the columns, however that feels wrong too. Syntactically am I doing something wrong here?

**Update:**
Just wanted to note: These records are duplicates based on a non-key / non-indexed field of ID listed above. The ID is a text field which although has the same value, it is a different case than the other data causing the issue.

SQL Left Join first match only

I have this table for documents (simplified version here):


| id   | rev   | content                              |
|------|-------|--------------------------------------|
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |


How do I select one row per id and only the greatest rev?  
With the above data, the result should contain two rows: `[1, 3, ...]` and `[2, 1, ..]`. I&#39;m using ***MySQL***.

Currently I use checks in the `while` loop to detect and over-write old revs from the resultset. But is this the only method to achieve the result? Isn&#39;t there a **SQL** solution?



SQL select only rows with max value on a column

Assume I have a `data.table` containing some baseball players:

    library(plyr)
    library(data.table)

    bdt &lt;- as.data.table(baseball)

For each group (given by player &#39;id&#39;), I want to select rows corresponding to the maximum number of games &#39;g&#39;. This is straightforward in `plyr`:

    ddply(baseball, &quot;id&quot;, subset, g == max(g))

What&#39;s the equivalent code for `data.table`?

I tried:

    setkey(bdt, &quot;id&quot;) 
    bdt[g == max(g)]  # only one row
    bdt[g == max(g), by = id]  # Error: &#39;by&#39; or &#39;keyby&#39; is supplied but not j
    bdt[, .SD[g == max(g)]] # only one row

This works:

    bdt[, .SD[g == max(g)], by = id] 


But it&#39;s is only 30% faster than `plyr`, suggesting it&#39;s probably not idiomatic.

Subset rows corresponding to max value by group using data.table

I&#39;m trying to get the ip, user, and most recent timestamp from a table which may contain both the current ip for a user and one or more prior ips. I&#39;d like one row for each user containing the most recent ip and the associated timestamp. So if a table looks like this:


    username      |  ip      |  time_stamp  
    --------------|----------|--------------  
    ted           | 1.2.3.4  | 10  
    jerry         | 5.6.6.7  | 12  
    ted           | 8.8.8.8  | 30  



I&#39;d expect the output of the query to be:

    jerry    |  5.6.6.7   |  12
    ted      |  8.8.8.8   |  30  

Can I do this in a single sql query? In case it matters, the DBMS is Postgresql.

SQL query to get most recent row for each instance of a given key

I have a table with `id`, `year` and `count`. 

I want to get the `MAX(count)` for each `id` and keep the `year` when it happens, so I make this query:

    SELECT id, year, MAX(count)
    FROM table
    GROUP BY id;

Unfortunately, it gives me an error: 

&gt; ERROR:  column &quot;table.year&quot; must appear in the GROUP BY clause or be
&gt; used in an aggregate function

So I try:

    SELECT id, year, MAX(count)
    FROM table
    GROUP BY id, year;

But then, it doesn&#39;t do `MAX(count)`, it just shows the table as it is. I suppose because when grouping by `year` and `id`, it gets the max for the `id` of that specific year.

So, how can I write that query? I want to get the `id`&#180;s `MAX(count)` and the year when that happens.

PostgreSQL MAX and GROUP BY

Let&#39;s say I have a table called `messages` with the columns:

    id | from_id | to_id | subject | message | timestamp

I want to get the latest message from each user only, like you would see in your FaceBook inbox before you drill down into the actual thread.

This query seems to get me close to the result I need:

    SELECT * FROM messages GROUP BY from_id

However the query is giving me the oldest message from each user and not the newest.

I can&#39;t figure this one out. 

How to get the latest record in each group using GROUP BY?

I have the following MySQL query that works perfectly fine. Except that I need to add a `FORCE INDEX` and I&#39;m unsure on where I have to do this. I tried just about every location and always receive a MySQL error. What am I doing wrong?

Here is the original query:

	$sql_select_recent_items = $db-&gt;query(&quot;SELECT * FROM (SELECT owner_id, product_id, start_time, price, currency, name, closed, active, approved, deleted, creation_in_progress FROM db_products ORDER BY start_time DESC) as resultstable
    WHERE resultstable.closed=0 AND resultstable.active=1 AND resultstable.approved=1 AND resultstable.deleted=0 AND resultstable.creation_in_progress=0
    GROUP BY resultstable.owner_id
    ORDER BY start_time DESC&quot;);

The query is constructed this way so that I can do the `ORDER BY` before the `GROUP BY`, in case you&#39;re wondering.

What I need to add is:

    FORCE INDEX (products_start_time)

I tried it just about everywhere without success, which leads me to believe that there&#39;s something more complex that I&#39;m missing?

FORCE INDEX in MySQL - where do I put it?

I have a table that is a collection entries as to when a user was logged on.

    username, date,      value
    --------------------------
    brad,     1/2/2010,  1.1
    fred,     1/3/2010,  1.0
    bob,      8/4/2009,  1.5
    brad,     2/2/2010,  1.2
    fred,     12/2/2009, 1.3

    etc..

How do I create a query that would give me the latest date for each user?

**Update:** I forgot that I needed to have a value that goes along with the latest date.

All Greatest N-per-Group Solutions on Gang of Coders

Total of 41 Greatest N-per-Group Solutions