Search for records using a sentence

Question

I would like to get all the records that have the most recent modification time. For example, taking this table:

|page_url|last_modified| --------- |abc.com |2010-10-01 10:00:00| |xyz.com |2010-10-04 12:10:00| |xyz.com |2010-10-04 12:00:00| |xyz.com |2010-10-04 10:00:00| |abc.com |2010-10-01 08:00:00| 

And the output should contain the following data:

 |page_url|last_modified| --------- |abc.com |2010-10-01 10:00:00| |xyz.com |2010-10-04 12:10:00| 

I tried using the sentence as shown below, but didn't work for me :(

 SELECT page_url, last_modified FROM my_table HAVING MAX(last_modified); 

Edit 1: I have 25 fields in my table. But, I think, I can’t apply the group to everyone. So what's now?

Answer 1

No need to use HAVING for this, only GROUP BY .

 SELECT page_url, MAX(last_modified) FROM my_table GROUP BY page_url 

UPDATE 1

 SELECT a.* FROM my_table a INNER JOIN ( SELECT page_url, MAX(last_modified) recentDate FROM my_table GROUP BY page_url ) b ON a.page_url = b.page_url AND a.last_modified = b.recentDate 

Answer 2

Try using group by and max() in select statement

 SELECT page_url, max(last_modified) FROM my_table group by page_url 

Additional Information:

• GROUP BY
• MAX
• HAVING

Answer 3

If you are using an RDBMS that has tuple-capable IN like Postgresql, MySql, and Oracle. You can use the following approach.

So, let's say you already have an existing view:

 create view page_latest as SELECT page_url, MAX(last_modified) recentDate FROM tbl GROUP BY page_url; select * from page_latest; 

Output...

 | PAGE_URL | RECENTDATE | --------------------------------------------- | abc.com | October, 01 2010 10:00:00+0000 | | xyz.com | October, 04 2010 12:10:00+0000 | 

... based on this data:

 | PAGE_URL | LAST_MODIFIED | MESSAGE | ------------------------------------------------------------------ | abc.com | October, 01 2010 10:00:00+0000 | no alarm | | xyz.com | October, 04 2010 12:10:00+0000 | no surprises | | xyz.com | October, 04 2010 12:00:00+0000 | fake plastic trees | | xyz.com | October, 04 2010 10:00:00+0000 | creep | | abc.com | October, 01 2010 08:00:00+0000 | thom yorke | 

You can find all the latest posts by reusing the above view using IN, which is part of the above RDBMSes: http://www.sqlfiddle.com/#!2/b8193/2

 select * from tbl where (page_url,last_modified) in (select page_url, recentDate from page_latest); 

Output:

 | PAGE_URL | LAST_MODIFIED | MESSAGE | ------------------------------------------------------------ | abc.com | October, 01 2010 10:00:00+0000 | no alarm | | xyz.com | October, 04 2010 12:10:00+0000 | no surprises | 

Not only the request is shorter, the easier it is to read. Although, if you don’t have a look, you can insert a “request” in your request: http://www.sqlfiddle.com/#!2/b8193/5

 select * from tbl where (page_url,last_modified) in (SELECT page_url, MAX(last_modified) recentDate FROM tbl GROUP BY page_url); 

This answer does not work on the Sql server. There are only a few databases that do not support tuple-capable IN , unfortunately Sql Server is one of them.

Answer 4

 SELECT z.page_url,z.last_modified FROM (SELECT page_url, MAX(last_modified) FROM my_table GROUP BY page_url) as z ORDER BY z.page_url 

Answer 5

How about these

  SELECT page_url,last_modified,other field FROM my_table ORDER BY page_url,last_modified 

OR

  SELECT distinct page_url,last_modified,other field FROM my_table ORDER BY page_url,last_modified