CODEWITHSUNDEEP
mysqlcountgroup-by
Steve Tran
Steve Tran

Reputation: 295

Select maximum date with group by another columns

I have a table like:

gold_2012

gold_city | gold_type | gold_cost | gold_selltime
--------------------------------------------------
city1     | type 1    | 41.23     | 2012-01-01   
city1     | type 1    | 42.23     | 2012-02-02   
city1     | type 1    | 40.23     | 2012-03-03   
city2     | type 2    | 43.23     | 2012-01-01   
city2     | type 2    | 45.23     | 2012-02-02   
city2     | type 2    | 47.23     | 2012-03-03   
city3     | type 3    | 48.23     | 2012-01-01   
city3     | type 3    | 49.23     | 2012-02-02   
city3     | type 3    | 44.23     | 2012-03-03

How can I get 1 last result order by gold_selltime desc each group by gold_city and gold_type.

I used this:

SELECT * , COUNT( * ) 
FROM  gold_2012
GROUP BY  gold_type ,  gold_city
ORDER BY gold_selltime DESC

but it didn't work.
I only have result like:

gold_city | gold_type | gold_cost | gold_selltime
--------------------------------------------------
city1     | type 1    | 41.23     | 2012-01-01   
city2     | type 2    | 43.23     | 2012-01-01   
city3     | type 3    | 48.23     | 2012-01-01

but I need it like:

gold_city | gold_type | gold_cost | gold_selltime
--------------------------------------------------
city1     | type 1    | 40.23     | 2012-03-03   
city2     | type 2    | 47.23     | 2012-03-03   
city3     | type 3    | 44.23     | 2012-03-03

Sorry! I forget sth! Please see my edited question above.

Upvotes: 6

Views: 6195

Answers (2)

Himanshu
Himanshu

Reputation: 32602

You can use MAX function for that:

SELECT gold_city, gold_type 
       , MAX(gold_selltime) AS gold_selltime, COUNT( * ) AS `COUNT`
FROM gold_2012 
GROUP BY gold_type , gold_city 
ORDER BY gold_selltime DESC

See this SQLFiddle

Note: You can convert your date using DATE_FORMAT function:

DATE_FORMAT(MAX(gold_selltime), '%Y-%m-%d') AS gold_selltime

See this SQLFiddle

UPDATE

As OP changed his/her requirement see this updated answer:

You can achieve that using the following query:

SELECT *
FROM gold_2012
WHERE gold_selltime IN
(
    SELECT MAX(gold_selltime) AS gold_selltime
    FROM gold_2012  
)

See this SQLFiddle

Upvotes: 12

sayannayas
sayannayas

Reputation: 774

Try this

SELECT gold_city,gold_type , MAX(gold_selltime)
FROM gold
WHERE gold_city IN (select distinct gold_city from gold) 
GROUP BY gold_city ,gold_type

Upvotes: 1

Related Questions