Mysql Order / Sort by before Group by

Submitted by n8coder on Sun, 08/30/2009 - 20:14

Every developer knows the horror of mysql sort/order by or select distinct using with group by. Mysql does group by before order by and you get mixed results not what you expected. This is a small solution with less performance problem:

SELECT * FROM 

(
select * from `my_table` order by timestamp desc
) as my_table_tmp

group by catid

order by nid desc

In this example we get latest news in each category. We create a temp table by sorting by timestamp and group by after it. It worked for me.

Bookmark/Search this post with

n8coder's blog

Comments

This is a really good

Submitted by Robin (not verified) on Tue, 10/13/2009 - 11:01.

This is a really good solution, I think it will be clear to everyone.

nice one

Submitted by flash (not verified) on Thu, 11/05/2009 - 22:31.

i dont know how you came up with this fix but thanks, its awesome and works great

very nice...

Submitted by john (not verified) on Wed, 02/24/2010 - 13:31.

Just what I needed! Thank you!

Thank you so much - it was

Submitted by Anonymous (not verified) on Mon, 08/09/2010 - 06:06.

Thank you so much - it was driving me nuts. This is great!

Tnx, I was loosing my head with GROUP BY sorting problem

Submitted by Alan Ristić (not verified) on Wed, 08/25/2010 - 13:51.

Really, you saved my hair here ;)
Internalls must be known to came up with this solution. I doubt I'd come to this any time soon if ever. Tnx again.

GREAT! PERFECT! THANK YOU!! I

Submitted by Anonymous (not verified) on Mon, 01/03/2011 - 02:57.

GREAT! PERFECT! THANK YOU!!

I wanted to pass on one more thing. My initial query had a few JOINs in it. So the field I was grouping by looked like "Table1.Field1" in the inner SELECT statement. It didn't work until I changed it to "Table1.Field1 as ThisGroup" and for the final line used "GROUP BY my_table_tmp.ThisGroup". (I know - probably obvious to most database programmers, but I thought I'd pass it on so other newbies like me don't have to struggle so much. *grin*)

Thanks again!!

marvelous

Submitted by Martin (not verified) on Sun, 02/06/2011 - 23:43.

so very simple, thank you! keeping the inner statement clear of any grouping while do constraints and ordering there lets it also look very clear and tidy (compared to using subselect-constraints).

Thanks a ton for this

Submitted by Anonymous (not verified) on Fri, 02/18/2011 - 13:26.

Thanks a ton for this solution.... Tried a lot to find the same everywhere, but failed :((

Thanks a lot again...

Thank You, Thank You .....

Submitted by Anonymous (not verified) on Wed, 02/23/2011 - 10:48.

Spend yesterday all evening solving this problem.
This morning found your post and my problem vanished in 5 minutes.

Thank's again.

Awesome

Submitted by Wall Tearer (not verified) on Fri, 03/04/2011 - 19:53.

Thanks, dude, your solution gave me the right direction of solving my own problem.
I had a nested tree, and I needed to get a set of rows with their parents.
So, you helped me to solve the core part of the query:

 
...............
LEFT JOIN 
	(select * from `communicate` where `communicate`.ROOT = 14 ORDER BY `communicate`.LFT DESC) `parent` 
	ON (`parent`.LFT < `t`.LFT AND `parent`.RGT > `t`.RGT AND `parent`.ROOT = `t`.ROOT)
............
GROUP BY `t`.ID
.............

Helped me too!

Submitted by sharky51 (not verified) on Wed, 04/06/2011 - 18:58.

Hey! Great post, it helped me to solve my problem, too. Thanks and cheers!

awesome so clear and so

Submitted by bhawani (not verified) on Fri, 04/08/2011 - 07:25.

awesome so clear and so simple good job dude...

This is simpler, and seems to work.

Submitted by Anonymous (not verified) on Thu, 05/05/2011 - 17:45.

SELECT field1,field2,field3,MAX(timestamp) FROM
MY_TABLE
GROUP BY field1,field2;

Ir brings me all the records grouped by field1 and field2 with the most updated timestamp.

Much simpler and seems to be working for me. Do you see any problem there?

sorting before group by

Submitted by wan (not verified) on Wed, 06/15/2011 - 09:41.

Thank you soooo much. Keep posting. It helps a lot.

Many hours of headache gone in a few seconds.

Submitted by Zagger (not verified) on Sat, 08/13/2011 - 14:59.

Thank you so much for this. I was going out of my mind trying to solve this problem, and your solution did it. Simple and effective.

Thanks a lot

Submitted by Ashish Saxena (not verified) on Tue, 08/30/2011 - 12:02.

It's really a great solutions
I have searched almost 100 result of google page
but can't find the perfect solution.

It's great
thanks a lot

Thanks!

Submitted by julius (not verified) on Wed, 12/21/2011 - 19:28.

This works and works faster than using a select query as a column, which was the workaround i previously used.

Awesome!!!

Wonderful

Submitted by Anonymous (not verified) on Wed, 05/16/2012 - 15:34.

Can I call you MASTER ?

a bit dangerous

Submitted by vaclav (not verified) on Wed, 05/16/2012 - 15:48.

interesting solution, but imho it depends on behavior, that the group is created from the first occurence of group member. Is it somewhere documentet? I don't think so.

It seems to me a bit dangerous.