Postgresql select min Date value

Question

These are my tables:

table: tickets

• ticketid: serial
• userid: integer
• dateticket: date
• timeticket: time

table: users

• userid: serial
• username: varchar
• password: varchar

These are my data:

    userid  username  password
    1        user1         123
    2        user2         123

ticketid  userid  dateticket timeticket
    1        1    2015-05-27 14:47:14
    2        1    2015-05-27 14:47:15
    3        1    2015-05-27 14:47:16
    4        2    2015-05-27 14:47:17
    5        2    2015-05-27 14:47:18
    6        2    2015-05-12 14:47:19

I want to show the amount of ticket that is assigned to a user with the userid and minimum date with the time.

My current query is:

select 
    min(t.dateticket) as mydate,
    (select min(ti.timeticket) as mytime from tickets ti where ti.userid=t.userid),
    t.userid,
    count(t.ticketid) as my_all
from
    tickets t
group by 
    t.userid
order by 
    my_all asc, 
    mydate asc, 
    mytime asc;

The current result is:

        mydate     mytime     userid  my_all
      2015-05-27   14:47:14     1        3
      2015-05-12   14:47:17     2        3


The expected result is:

        mydate     mytime     userid  my_all
      2015-05-12   14:47:19     1        3
      2015-05-27   14:47:14     2        3

Answer 1

I think this will do what you want:

select distinct on (userid) dateticket, timeticket, userid,
       count(*) over (partition by userid) as my_all
from tickets t
order by userid, dateticket, timeticket;

distinct on returns the first record in each group, based on the order by. The trick is to get the count of all the tickets using a window function.

EDIT:

For an additional sort, use a subquery:

select t.*
from (select distinct on (userid) dateticket, timeticket, userid,
             count(*) over (partition by userid) as my_all
      from tickets t
      order by userid, dateticket, timeticket
     ) t
order by my_all, dateticket, timeticket.