GROUP BY conditions

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

GROUP BY conditions

Post by pickle »

Hi all,

I'm doing a query on a table. One of the columns, `type` is one of 3 values, the rest can be entirely unique/arbitrary. Right now, I'm doing a GROUP BY on `type`, because I only want one row returned per `type` value. MySQL is giving me my 3 rows, but it's always returning the first row for a given type. I want the last row.

So, if I've got 3 row of type "permanent", one with a `from_date` of 1995, one of 2000, and one of 2003, I want the row returned to be the 2003 row. I know I can brute force this by getting all rows and plugging through them in PHP, but I'm wondering if there's a way to do it just in the query.

Thanks.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: GROUP BY conditions

Post by Eran »

I've written an article recently about how to achieve the results you are looking for, have a look:
http://www.techfounder.net/2010/03/12/f ... ith-mysql/
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: GROUP BY conditions

Post by pickle »

Looks promising. I was hoping to avoid sub-queries (rationally or not - they just seem like a cludge fix), but it seems like that's the only solution. Thanks.
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
Post Reply