Lets say we have a log table containing a record of page views for an intranet:
| DateStamp | IpAddress | Page |
|---|---|---|
| 20/06/2011 | 192.168.0.10 | home |
| 21/06/2011 | 192.168.0.11 | about |
| 21/06/2011 | 192.168.0.10 | home |
| 22/06/2011 | 192.168.0.12 | home |
| 22/06/2011 | 192.168.0.10 | home |
| 23/06/2011 | 192.168.0.11 | about |
To query this table to show the earliest date when each page was viewed requires just a simple group-by query:
SELECT Page, min(DateStamp) as FirstViewed
FROM LogTable
GROUP BY Page
FROM LogTable
GROUP BY Page
which will give:
| Page | FirstViewed |
|---|---|
| about | 21/06/2011 |
| home | 20/06.2011 |
But if we wanted to include the IP address associated with each of those first page views, then a simple join will no longer suffice and our query becomes significantly more complex.
SELECT detail.Page, detail.DateStamp as FirstViewed, detail.IpAddress
FROM LogTable detail
INNER JOIN (
SELECT Page, min(DateStamp) as FirstViewed
FROM LogTable
GROUP BY Page) inside
ON inside.Page = detail.Page
AND inside.FirstViewed = detail.DateStamp
FROM LogTable detail
INNER JOIN (
SELECT Page, min(DateStamp) as FirstViewed
FROM LogTable
GROUP BY Page) inside
ON inside.Page = detail.Page
AND inside.FirstViewed = detail.DateStamp
This returns:
| Page | FirstViewed | IpAddress |
|---|---|---|
| home | 20/06/2011 | 192.168.0.10 |
| about | 21/06/2011 | 192.168.0.11 |
This can be easily done using qualify as below:
select Page, DateStamp as FirstViewed, IpAddress
from LogTable
qualify ROW_NUMBER() OVER (partition by Page order by DateStamp )=1
;