CODEWITHSUNDEEP
sqlpostgresqlgroup-bydistinctgreatest-n-per-group
Raphael Telatim
Raphael Telatim

Reputation: 153

SELECT DISTINCT or GROUP BY on field

I have a table on my DB that record every call made by employees all over the day.

Every record is a call made by branch lines.

If a branch made 50 calls a day we will have 50 records on DB:

branch |            date            
-------+----------------------------
 72489 | 2015-12-03 08:03:58
 34002 | 2015-12-03 08:03:59
 78700 | 2015-12-03 08:05:37
 78700 | 2015-12-03 08:05:53
 78700 | 2015-12-03 08:05:57
 78700 | 2015-12-03 08:06:24

What I would like to do is take the first call of the day of every branch line.

I tried to use DISTINCT:

SELECT DISTINCT branch, date FROM table WHERE date::date='2015-12-03'::date;

Supposedly DISTINCT would avoid repetion on branch, but the result was:

branch |            date           
-------+----------------------------
 34002 | 2015-12-03 07:58:24
 34002 | 2015-12-03 08:00:40
 59754 | 2015-12-03 08:01:31
 34002 | 2015-12-03 08:01:56
 59754 | 2015-12-03 08:02:09
 57764 | 2015-12-03 08:02:17
 59754 | 2015-12-03 08:02:28

I have some repetions on there.

What I like to have on my result is:

branch |            date            
-------+----------------------------
 34002 | 2015-12-03 07:58:24
 59754 | 2015-12-03 08:01:31
 59754 | 2015-12-03 08:02:09

I tried with GROUP BY:

SELECT branch, date FROM table WHERE date::date='2015-12-03'::date GROUP BY branch;

But get this error:

ERROR: column "table.date" must appear in the GROUP BY clause or be used in an aggregate function.

Can someone help-me?

PS: Sorry for the english, my bad.

Upvotes: 0

Views: 149

Answers (3)

user330315
user330315

Reputation:

The cast using ::date implies you are using Postgres. In this case getting the "the first call of the day of every branch line" is quite easy by using the distinct on() operator: 

SELECT DISTINCT ON (branch) branch, date 
FROM table 
WHERE date::date = '2015-12-03'::date
ORDER BY branch, date;

Another possibility to solve this kind of query is to use a window function:

select branch, date
from (
   select branch, date,  
          row_number() over (partition by branch order by date) as rn
   where cast(date as date) = date '2015-12-03'
) t
where rn = 1
order by branch;

The distinct on () solution is Postgres specific, the second solution is ANSI SQL (using ANSI a date literal and ANSI casting)

Upvotes: 1

jarlh
jarlh

Reputation: 44766

Select a row as long as no other row from the same date is earlier:

SELECT branch, date
FROM table t1
WHERE not exists (select 1 from table t2
                  where cast(t1.date as date) = cast(t2.date as date)
                    and t2.date < t1.date)

ANSI SQL compliant.

Upvotes: 0

Kees
Kees

Reputation: 1418

Possible solution:

SELECT 
    branch, 
    (   
        select 
            date 
        from 
            table tb 
        where 
            tb.branch = table.branch
    ) as date 
FROM 
    table 
GROUP BY
    branch
WHERE 
    date::date='2015-12-03'::date;

Upvotes: 0

Related Questions