CODEWITHSUNDEEP
phpmysql
TommyB
TommyB

Reputation: 3

MySQL query based on highest value but column is VARCHAR

Running into a bit of a problem here. We have a system where we track our website hostings and I'm developing a frontend portal on this already pre-existing system.

In the database, the table looks something like:

id | company_id | renewal | domain_name

and an example couple entries in a MySQL database: 

 1 | 5 | June 2014 | www.example.com
 2 | 5 | June 2015 | www.example.com
 3 | 5 | June 2016 | www.example.com
 4 | 5 | June 2017 | www.example.com
 5 | 5 | June 2018 | www.example.com
 6 | 5 | June 2014 | www.stackoverflow.com
 7 | 5 | June 2015 | www.stackoverflow.com
 8 | 5 | June 2016 | www.stackoverflow.com
 9 | 5 | June 2017 | www.stackoverflow.com
10 | 5 | June 2018 | www.stackoverflow.com

I am wanting to SELECT * where the company_id = 5 - that's no problem. I then want to show all domains where the renewal is the highest - basically I want to return:

 5 | 5 | June 2018 | www.example.com
10 | 5 | June 2018 | www.stackoverflow.com

Getting unique domains is simple, I've achieved that by GROUP BY(domain_name), but am clueless on sorting by renewal, especially because it isn't a simple int, rather a combination.

Upvotes: 0

Views: 36

Answers (1)

seven77
seven77

Reputation: 380

I would definitely recommend to convert this table to something with a real date in it, so you can sort it. For the time being, you could use MySQL's STR_TO_DATE function, which tries to parse the date according to a given format.

Here that would be:

SELECT domain_name, MAX(STR_TO_DATE(renewal_date, '%M %Y')) AS max_renewal_date
FROM table
WHERE company_id = 5
GROUP BY domain_name

Upvotes: 2

Related Questions