Message-Id: <5.2.1.1.0.20030723152501.00a0cd90@pop6.sympatico.ca>
Date: Wed, 23 Jul 2003 16:45:24 -0400
To: pgsql-sql@postgresql.org
From: Frank Bax <fbax@sympatico.ca>
Subject: strange "order by" request
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

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;