Geek asks and answers

Question with mysql nested query

I am trying to create a mysql query to retrieve a row for each city using the latest trans_date and trans_count. I want to return only one row for each city.

Table operations

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

Data examples

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

Required query results (one row for a city with the most recent date and quantity 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 found returns this result set that I am looking for. Any help was appreciated.

Thank,

-Scott

+3
source share
6 answers

What 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 not add comments to other answers, but you need nesting, because otherwise the group is applied before the order and does not give the expected answer.

+3
source 
SELECT id, 
       trans_date, 
       trans_city, 
       trans_count 
FROM YourTable 
GROUP BY trans_city
ORDER BY trans_date DESC, trans_count;
+1
source

, SQL Server, , .

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
+1
SELECT DISTINCT `trans-city` 
FROM `table` 
ORDER BY `trans_date` DESC, `trans_count` DESC;
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
0

start with an internal advance request for the latest city date .. then join this last entry in the 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
0
source

More articles:


All Articles