<div>Hi Muhannad,</div><div> </div><div>first_value and last_value functions can be used for these purpose. But query should be rewrited as below. Also for this case I'm not sure which one is better, sub query or window functions.</div><div> </div><div>SELECT DISTINCT employee_id, day, sum(total_hours) over w1, min(sign_in) OVER w1 as first_sign_in, max(sign_in) OVER w1 as last_sign_in, first_value(device_id) OVER w2 as first_sign_in_device, first_value(device_id) OVER w3 as last_sign_in_device</div><div><div>FROM <span>bioemployee </span></div><div><span>WHERE sign_in BETWEEN X and Y</span></div><div>WINDOW w1 as (PARTITION BY employee_id,day),w2 as (PARTITION BY employee_id,day ORDER BY sign_in ASC),w3 as (PARTITION BY employee_id,day ORDER BY sign_in DESC)</div><div>ORDER BY day;</div></div><div> </div><div>Best regards.</div><div> </div><div>İyi çalışmalar.</div><div>Samed YILDIRIM</div><div> </div><div>20.04.2017, 20:35, "Muhannad Shubita" <muhannadshubita(at)gmail(dot)com>:</div><blockquote type="cite"><div>Thanks Samed,<div> </div><div>one more question, what if I had other columns that cannot be used with an aggregate function (TEXT-based for example) but still needed to be paired with the first & last sign in? for instance, a randomly generated md5-ed ID by the sign in device that needs to be displayed as a reference:</div><div> </div><div><div style="font-size:12.8px;">Day <span style="white-space:pre-wrap;"> </span>Employee<span style="white-space:pre-wrap;"> </span>Total-Hours<span style="white-space:pre-wrap;"> </span> First-Sign-In <span style="white-space:pre-wrap;"> device-id-of-first-sign-in </span>Last-Sign-In device-id-of-last-sign-in</div><div style="font-size:12.8px;"> </div><div style="font-size:12.8px;">20/4<span style="white-space:pre-wrap;"> </span>emp1<span style="white-space:pre-wrap;"> </span>4<span style="white-space:pre-wrap;"> </span>8:22<span style="white-space:pre-wrap;"> 202cb962ac5.. </span>3:25 152d234b70..</div></div><div style="font-size:12.8px;"> </div><div> <div>On Thu, Apr 20, 2017 at 9:18 PM, Samed YILDIRIM <span><<a target="_blank" href="mailto:samed(at)reddoc(dot)net">samed(at)reddoc(dot)net</a>></span> wrote:<blockquote style="margin:0 0 0 0.8ex;border-left:1px #ccc solid;padding-left:1ex;"><div>Hi Muhannad,</div><div> </div><div>Did you try using MIN and MAX function? I guess that following query solves your problem.</div><div> </div><div><div>select employee_id, day, sum(total_hours) as total_hours, (select name from employee where id = employee_id) as emp_name, <em><strong>min(sign_in) as first_sign_in, max(sign_in) as last_sign_in</strong></em></div><div><span>from bioemployee where sign_in BETWEEN X and Y GROUP BY employee_id, day ORDER BY day;</span></div><div><span> </span></div><div>Best regards.</div></div><div> </div><div> </div><div> </div><div> </div><div>İyi çalışmalar.</div><div>Samed YILDIRIM</div><div> </div><div> </div><div> </div><div>20.04.2017, 19:56, "Muhannad Shubita" <<a target="_blank" href="mailto:muhannadshubita(at)gmail(dot)com">muhannadshubita(at)gmail(dot)com</a>>:</div><div><div><blockquote type="cite"><div><div>Good day,</div><div> </div><div>I have a table with biometric info about employees (let's call it bioemployee):</div><div> </div><div>id serial not null -- primary key</div><div>employee_id -- foreign key references employee table</div><div>day char(8) -- YY-MM-DD</div><div>sign_in TIMESTAMP</div><div>total_hours INTEGER</div><div> </div><div>I want to display details grouped by day & employee Id, for example:</div><div> </div><div> </div><div>Day <span style="white-space:pre-wrap;"> </span>Employee<span style="white-space:pre-wrap;"> </span>Total-Hours<span style="white-space:pre-wrap;"> </span> First-Sign-In <span style="white-space:pre-wrap;"> </span>Last-Sign-In</div><div> </div><div>20/4<span style="white-space:pre-wrap;"> </span>emp1<span style="white-space:pre-wrap;"> </span>4<span style="white-space:pre-wrap;"> </span>8:22<span style="white-space:pre-wrap;"> </span>3:25</div><div>21/4<span style="white-space:pre-wrap;"> </span>emp1<span style="white-space:pre-wrap;"> </span>7<span style="white-space:pre-wrap;"> </span>9:00<span style="white-space:pre-wrap;"> </span>4:11</div><div>21/4<span style="white-space:pre-wrap;"> </span>emp2<span style="white-space:pre-wrap;"> </span>2<span style="white-space:pre-wrap;"> </span>11:00<span style="white-space:pre-wrap;"> </span>01:11</div><div> </div><div> </div><div>I created a pgsql function to get the details through the below query:</div><div> </div><div>select employee_id, day, sum(total_hours) as total_hours, (select name from employee where id = employee_id) as emp_name</div><div>from bioemployee where sign_in BETWEEN X and Y GROUP BY employee_id, day ORDER BY day;</div><div> </div><div>now the problem is with getting First-Sign-In & Last-Sign-In per group (employee & day), I have currently implemented it in a FOR loop:</div><div> </div><div>for RECORD in query LOOP</div><div>--First-Sign-In</div><div>select sign_in from bioemployee where employee_id = -- and day = -- and sign_in BETWEEN X and Y ORDER BY sign_in LIMIT 1;</div><div>--Last-Sign-In</div><div>select sign_in from bioemployee where employee_id = -- and day = -- and sign_in BETWEEN X and Y ORDER BY sign_in DESC LIMIT 1;</div><div> </div><div>return next json_build_object(first_sign_in, last_sign_in, ..rest of details);</div><div>END LOOP</div><div> </div><div>but If I had 100 employees over a span of 30 days, this would be 6000 queries inside the loop! which I am sure you would agree is an overkill</div><div> </div><div> </div><div>is there a better way to do this? </div><div> </div><div> </div><div>Thanks.</div></div></blockquote></div></div></blockquote></div> <div> </div>--<div><div><font face="monospace, monospace">Regards,</font><div><font face="monospace, monospace">Muhannad</font></div></div></div></div></div></blockquote>