Mysql Order / Sort by before Group by

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.

Comments

This is a really good

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

nice one

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

very nice...

Just what I needed! Thank you!

Thank you so much - it was

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

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

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

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

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

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

Thanks a lot again...

Thank You, Thank You .....

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

Thank's again.

Awesome

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!

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

awesome so clear and so

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

This is simpler, and seems to work.

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

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

Many hours of headache gone in a few seconds.

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

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!

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

Awesome!!!

Wonderful

Can I call you MASTER ?

a bit dangerous

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.

Post new comment

The content of this field is kept private and will not be shown publicly.
  • Allowed HTML tags: <a> <b> <pre> <h1> <h2> <h3> <h4> <h5> <h6> <em> <strong> <cite> <code> <ul> <ol> <li> <dl> <dt> <dd> <div> <style><img> <br> <blockquote>
  • Lines and paragraphs break automatically.
  • You may insert videos with [video:URL]

More information about formatting options

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.

CafeWebmaster.com(CW) is a free online community for webdevelopers and beginners. Anybody can share their code, articles, tips, tutorials, code-examples or other webdesign related material on the site. Newbies can submit their questions and reply to existing questions. CW does not guarantee or warrant reliability of code, data and information published on the site. Use the site on your own risk. The site takes no responsibility of direct or indirect loss or any kind of harm to its users. The site also doesn't take responsibility of infected files or source code with any kind of infection or viruses, worms, spywares, malwares, trojan horses. CW reserves the right to edit, move, or delete any of content for any reason.