mysql nested query question

Question

I'm trying to design a mysql query to retrieve the row for each city with the most recent trans_date and trans_count. I only want to return a single row per city.

Table transactions

------------------
id = integer
trans_date = date
trans_city = varchar
trans_count = integer

Sample Data

--------------
id       trans_date     trans_city     trans_count
--       ----------     ----------     -----------
1        2011-01-10     seattle        2104
2        2011-04-15     seattle        2072
3        2011-05-30     seattle        2057
4        2010-04-27     houston        5622
5        2010-04-30     houston        241
6        2010-05-25     houston        261

Desired Query Results (one row per city with the most recent date and count for that city)

---------------------
id       trans_date     trans_city     trans_count
--       ----------     ----------     -----------
3        2011-05-30     seattle        2057
6        2010-05-25     houston        261

None of the samples I have found return this result set I'm looking for. Any help appreciated.

Thanks,

-Scott

Johan · Accepted Answer · 2011-06-15 22:58:14Z

3

How about

SELECT * FROM 
  (
  SELECT id,trans_date,trans_city,trans_count 
  FROM transactions 
  ORDER BY trans_Date DESC) X 
GROUP BY trans_city

I can't add comments on the other answers but you need the nesting because otherwise the group by applies before the order by and doesn't give you the expected answer.

edited Jun 15, 2011 at 22:58

Johan

77.4k28 gold badges204 silver badges346 bronze badges

answered Jun 15, 2011 at 22:50

Bob Vale

18.5k1 gold badge44 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Johan Over a year ago

+1 @Bob, creative solution to fix the random picking of rows by the group by clause that MySQL allows for.

jwd · Accepted Answer · 2011-06-15 23:22:49Z

1

Here's a version that works on SQL Server, just for posterity's sake since the upvoted solution doesn't.

SELECT
    t.*
FROM
    transactions AS t,
    (SELECT
        MAX(trans_date) AS max_date
    FROM
        transactions
    GROUP BY trans_city) AS subquery
WHERE
    subquery.max_date = t.trans_date

answered Jun 15, 2011 at 23:22

jwd

11.4k4 gold badges48 silver badges78 bronze badges

Comments

kinakuta · Accepted Answer · 2011-06-15 23:33:19Z

1

SELECT id, 
       trans_date, 
       trans_city, 
       trans_count 
FROM YourTable 
GROUP BY trans_city
ORDER BY trans_date DESC, trans_count;

edited Jun 15, 2011 at 23:33

answered Jun 15, 2011 at 22:45

kinakuta

9,0371 gold badge41 silver badges48 bronze badges

1 Comment

Björn Over a year ago

The group by clause should come before the order by clause

Jacob Eggers · Accepted Answer · 2011-06-15 22:48:30Z

0

with distinct_cities ( select max(id) as id, trans_city
from table
group by trans_city)

select t.id, t.trans_date, t.trans_city, t.trans_count
from table t join distinct_cities d on t.id = d.id

answered Jun 15, 2011 at 22:48

Jacob Eggers

9,3282 gold badges27 silver badges43 bronze badges

Comments

Johan · Accepted Answer · 2011-06-15 23:00:34Z

0

SELECT DISTINCT `trans-city` 
FROM `table` 
ORDER BY `trans_date` DESC, `trans_count` DESC;

edited Jun 15, 2011 at 23:00

Johan

77.4k28 gold badges204 silver badges346 bronze badges

answered Jun 15, 2011 at 22:46

carlbenson

3,2176 gold badges38 silver badges55 bronze badges

Comments

DRapp · Accepted Answer · 2011-06-16 01:58:46Z

0

start with an inner pre-query for the last date per city.. then join based on that most recent entry per city

select t2.*
   from
      ( select t1.trans_city,
               max( t1.trans_date ) as MostRecentDate
           from 
               Transactions t1
           group by
               t1.trans_city ) PreQuery

      join Transactions t2
         on PreQuery.Trans_City = t2.Trans_City
         and PreQuery.MostRecentDate = t2.Trans_Date

answered Jun 16, 2011 at 1:58

DRapp

48.3k13 gold badges80 silver badges149 bronze badges

Collectives™ on Stack Overflow

mysql nested query question

6 Answers 6

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related