Better mode for do a select with group by

Posted by Luca Romagnoli on Stack Overflow See other posts from Stack Overflow or by Luca Romagnoli
Published on 2010-03-26T23:29:19Z Indexed on 2010/03/26 23:33 UTC
Read the original article Hit count: 173

Filed under:

group-by

|

select

|

mysql

Hi i've wrote a query that works:

     SELECT `comments`.* FROM `comments` 
RIGHT JOIN (SELECT MAX( id ) AS id, core_id, topic_id 
FROM comments GROUP BY core_id, topic_id order by id desc) comm 
ON comm.id = comments.id LIMIT 10

I want know if is possible and how rewrite it for get better performance.

thanks

© Stack Overflow or respective owner

Related posts about group-by

Twitter Bootstrap styling conflicts with plug-ins like jqGrid and other third part libraries

as seen on Geeks with Blogs - Search for 'Geeks with Blogs'
Issues:The concern is that the Twitter Bootstrap framework is that some of their css selectors are simply too generic and have incompatibility issues and conflicts with most third party plug-ins and css libraries, like jQuery-UI and jqGrid.My most pressing concern is only with the generic selector… >>> More
ASA hairpining: I basicaly want to allow 2 spokes to be able to communicate with each other.

as seen on Server Fault - Search for 'Server Fault'
ASA Spoke to Spoke Communication I have been looking at spke to spoke comms or "hairpining" for months and have posted on numerouse forums but to no avail. I have a Hub and spoke network where the HUB is an ASA Firewall version 8.2 * I basicaly want to allow 2 spokes to be able to communicate with… >>> More
Using net group to add users to a AD group

as seen on Server Fault - Search for 'Server Fault'
Hi, I'm trying to add users to the an Active Directory group using net group. We use Domain Local groups for everything. When I go to run the command net group "group name" "username" /add /domain it returns "The group name could not be found. More help is available by typing NET HELPMSG 2220." If… >>> More
rsync set group owner, group permission

as seen on Super User - Search for 'Super User'
I want to use rsync to transfer files from my computer to a remote Linux system. Regardless of the local file's group ownership, I want to set these values on the remote side. If I was on the remote Linux system, I could create the directory and set the ownership and permissions as: mkdir my_directory chown… >>> More
Specify which row to return on SQLite Group By

as seen on Stack Overflow - Search for 'Stack Overflow'
I'm faced with a bit of a difficult problem. I store all the versions of all documents in a single table. Each document has a unique id, and the version is stored as an integer which is incremented everytime there is a new version. I need a query that will only select the latest version of each document… >>> More

Related posts about select

HTML muliple select should look like HTML select

as seen on Stack Overflow - Search for 'Stack Overflow'
Hi I am trying to use a HTML select box with 'multiple' select options and size to 1 as below ` <SELECT NAME="toppings" MULTIPLE SIZE=5> <OPTION VALUE="mushrooms">mushrooms <OPTION VALUE="greenpeppers">green peppers </SELECT> When the size is set to 1 small scrollbar appears… >>> More
MySQL Query Select using sub-select takes too long

as seen on Stack Overflow - Search for 'Stack Overflow'
I noticed something strange while executing a select from 2 tables: SELECT * FROM table_1 WHERE id IN ( SELECT id_element FROM table_2 WHERE column_2=3103); This query took approximatively 242 seconds. But when I executed the subquery SELECT id_element FROM table_2 WHERE column_2=3103 it… >>> More
SQL SERVER- IF EXISTS(Select null from table) vs IF EXISTS(Select 1 from table)

as seen on SQL Authority - Search for 'SQL Authority'
Few days ago I wrote article about SQL SERVER – Stored Procedure Optimization Tips – Best Practices. I received lots of comments on particular blog article. In fact, almost all the comments are very interesting. If you have not read all the comments, I strongly suggest to read them. Click here to… >>> More
Nesting Linq-to-Objects query within Linq-to-Entities query –what is happening under the covers?

as seen on Programmers - Search for 'Programmers'
var numbers = new int[] { 1, 2, 3, 4, 5 }; var contacts = from c in context.Contacts where c.ContactID == numbers.Max() | c.ContactID == numbers.FirstOrDefault() select c; foreach (var item in contacts) Console.WriteLine(item.ContactID); Linq-to-Entities… >>> More
Play Framework: Error getting sequence nextval using H2 in-memory database

as seen on Stack Overflow - Search for 'Stack Overflow'
As the title suggests, I get an error running Play 2.0.1 Tests using a FakeApplication w/ H2 in memory. I set up a basic unit test: public class ModelTest { @Test public void checkThatIndustriesExist() { running(fakeApplication(inMemoryDatabase()), new Runnable() { public… >>> More