#1
  1. A Change of Season
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Mar 2004
    Location
    Next Door
    Posts
    2,664
    Rep Power
    171

    Is there any alternative solution to write this query?


    Just curious:
    Code:
    SELECT category,
           title,
           created
    FROM   entries AS t
    WHERE  created = (SELECT Max(created)
                      FROM   entries
                      WHERE  category = t.category)
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,959
    Rep Power
    1014
    Hi,

    you can use a join or a constant subquery:

    Code:
    SELECT
    	entries.category
    	, entries.title
    	, entries.created
    FROM
    	(
    		SELECT
    			category
    			, MAX(created) AS last_created
    		FROM
    			entries
    		GROUP BY
    			category
    	) AS last_entries
    	JOIN
    		entries ON entries.category = last_entries.category AND entries.created = last_entries.last_created
    ;
    Code:
    SELECT
    	category
    	, title
    	, created
    FROM
    	entries
    WHERE (category, created) IN (
    	SELECT
    		category
    		, MAX(created)
    	FROM
    		entries
    	GROUP BY
    		category
    )
    ;
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".

IMN logo majestic logo threadwatch logo seochat tools logo