GROUP BY and ORDER BY help needed

Report · Feb 13, 2011

I have got an sql query where I have the need to pull out the latest copy of duplicate info, but what is happening is that the first instance is being displayed. The query I have is this:

SELECT *

FROM tbl_maincontenttext

WHERE fld_menusection = 3

GROUP BY fld_webpage

ORDER BY fld_timedate DESC

Basically, I have content that is listed under menu section 3, but within that I will have several copies of content that will relate to a specific webpage (eg: about us). What is currently happening is that GROUP BY is obviously grouping the similarly named 'about us', but it is pulling the first record it comes across out of the database rather than the latest updated record.

As you can see, I am trying to get the query to order by fld_timedate which is a CURRENT_TIMESTAMP, but it's not working!

I'm hoping that there is some sort of SQL that I am unaware of that will help me group by and display the latest update/content.

Thanks.

Mat

Report · Feb 14, 2011

It would help if you could show us the table definition. Your SQL statement is ambigous because you are selecting all table columns, yet only including one column in the group by clause. A SQL statement must contain all selected columns that are not aggregates. Most DBMS will return an error for this statement. Others that don't return an error will return unexpected results.