SQL return a Distinct column and the first date of the distinct column

Question

I have a database that contains multiple url and a date (created_at) associate with each of these url.
I would like to have something like:

Select DISTINCT url, "the first date of this url"
from database
where blabala

My problem is when a add the date to the select I get this:

/url/sdfsd  |   2014-07-19  
/url/sdfsd  |   2014-07-20  
/url/sdfsd  |   2014-07-25

And what I want is only:

/url/sdfsd  |   2014-07-19

I realise that i over simplified my problem but thanks to you guys i managed to find a solution

select req2.date, COUNT(DATE(req2.date)) as count
from (
    select hash_request -> 'PATH_INFO', min(DATE(created_at)) as date
        from (
            select *
            from request_statistics
            where LOWER(hash_request -> 'HTTP_USER_AGENT') LIKE '%google%'
         ) req1
    group by hash_request -> 'PATH_INFO'
     )  req2
group by req2.date
order by req2.date asc

i had difficulty grouping the date on all the unique url. now i have, for each day what is the amount of unique url of all the unique url

Start by reading about basics in the manual [here](http://www.postgresql.org/docs/current/interactive/queries-table-expressions.html#QUERIES-GROUP) and [here](http://www.postgresql.org/docs/current/interactive/functions-aggregate.html). — Erwin Brandstetter, Jul 31 '14 at 16:38

score 6 · Accepted Answer · answered Jul 31 '14 at 16:25

6

Are the records sorted?

select url, min(created_at)
from databaseTable
where blabala
group by url

answered Jul 31 '14 at 16:25

Conffusion

4,335
2
16
28

score 0 · Answer 2 · answered Jul 31 '14 at 16:27

0

In the event that there are other fields that you want as well, then the distinct on syntax may be what you want:

Select DISTINCT ON (url) d.*
from database d
where blabala
order by url, created_at asc;

answered Jul 31 '14 at 16:27

Gordon Linoff

1,242,037
58
646
786

score 0 · Answer 3 · answered Jul 31 '14 at 16:27

0

Could you use "LIMIT" to only return one row?

SELECT url, created_at FROM table WHERE blablabla LIMIT 1

You could also use ORDER BY to select the earliest or most recent date.

Here's more information on LIMIT

answered Jul 31 '14 at 16:27

kkewley

41
1

score 0 · Answer 4 · answered Jul 31 '14 at 16:35

The issue with distinct is that it returns a distinct row. So when you have multiple records that are all the same except for the date they are all seen as distinct because the dates are different (i.e. the whole record is not the same as any other). So basically you can use the distinct on method suggested by Gordon or the min method suggested by Conffusion. My preference personally would be distinct on though. It's more robust.

SQL return a Distinct column and the first date of the distinct column

4 Answers4