[SQL] strange "order by" request

Frank Bax Wed, 23 Jul 2003 13:49:00 -0700

Two tables - employee and timesheet simple enough. Each table is more complex than example before. timesheet contains multiple rows per day because the "other" fields are different for various rows on same day/employee. My selection criteria actually uses some of these other fields. lo_shift and hi_shift are always on the same date for a given row.

In the output, I *must* have all rows for an employee together, the sequence of employees should be based on lowest value of "lo_shift" for each employee. How do I code such a select?

Here are some commands to create tables & sample data:
CREATE TABLE "employee" (
        "emp" character(6) NOT NULL,
        "last" character varying(64),
        "first" character varying(64)
);

CREATE TABLE "timesheet" (
        "emp" character(6) NOT NULL,
        "dept" character(2) NOT NULL,
        "lo_shift" timestamp,
        "hi_shift" timestamp
);

insert into employee(emp,last,first) values('091006','Clarke','Cynthia');
insert into employee(emp,last,first) values('096005','Mullins','Denise');
insert into employee(emp,last,first) values('089068','Johnson','Michelle');
insert into employee(emp,last,first) values('098036','Zandstra','Nicole');

insert into timesheet(emp,dept,lo_shift,hi_shift) values('091006','10','2003-07-17 00:00','2003-07-17 07:59'); insert into timesheet(emp,dept,lo_shift,hi_shift) values('091006','10','2003-07-17 08:00','2003-07-17 11:59'); insert into timesheet(emp,dept,lo_shift,hi_shift) values('096005','10','2003-07-17 07:30','2003-07-17 08:59'); insert into timesheet(emp,dept,lo_shift,hi_shift) values('096005','10','2003-07-17 09:00','2003-07-17 17:59'); insert into timesheet(emp,dept,lo_shift,hi_shift) values('098036','10','2003-07-17 13:30','2003-07-17 19:29'); insert into timesheet(emp,dept,lo_shift,hi_shift) values('098036','10','2003-07-17 19:30','2003-07-17 21:29'); insert into timesheet(emp,dept,lo_shift,hi_shift) values('089068','10','2003-07-17 14:00','2003-07-17 17:59'); insert into timesheet(emp,dept,lo_shift,hi_shift) values('089068','10','2003-07-17 18:00','2003-07-17 21:59'); insert into timesheet(emp,dept,lo_shift,hi_shift) values('000032','90','2003-07-18 18:00','2003-07-17 23:59');

SELECT emp.emp, emp.last, emp.first, ts.lo_shift, ts.hi_shift
  FROM timesheet ts, employee emp WHERE ts.emp = emp.emp
   AND ts.dept='10' AND ts.lo_shift::date = '2003-07-17'
 ORDER BY emp.first, emp.last, emp.emp, ts.lo_shift, ts.hi_shift;


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[SQL] strange "order by" request

Reply via email to