CODEWITHSUNDEEP
sqldatabase
Saurabh Saxena
Saurabh Saxena

Reputation: 3205

Find records using having clause

I would like to retrieve all records having 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 output should contain following data:

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

I have tried using having clause like below but not working for me :(

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

Edit 1 : I am having 25 fields in my table to use. But, I guess I can't apply group by for all. What to do now?

Upvotes: 4

Views: 529

Answers (5)

Michael Buen
Michael Buen

Reputation: 39393

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

So let's say you already have existing view like this:

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 latest messages by re-using the above view with tuple-capable IN of aforementioned 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 query is shorter, it's easier to read. Though if you don't have a view lying around, you can inline a "view" in your query: 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 doesn't work in Sql Server. There are only few database that doesn't support tuple-capable IN, unfortunately Sql Server is one of them

Upvotes: 2

Zo Has
Zo Has

Reputation: 13028

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

Upvotes: 0

John Woo
John Woo

Reputation: 263723

No need to use HAVING on this, only GROUP BY clause.

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

Upvotes: 4

nnnn
nnnn

Reputation: 1051

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

Upvotes: 0

Robert
Robert

Reputation: 25753

Try to use group by and max() in select statement

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

More information:

Upvotes: 2

Related Questions