Hi Simon,
Here's a less gruesome version - no cases. I've given no thought to
performance comparisons.
Thanks for the two great solutions you posted. They certainly achieve
the desired result with the simplified sample I gave. However, in the
broader reality, it doesn't quite satisfy my situation since the
rowids aren't necessarily in the order that they appear in the final
view.
Here's a broader example.
Say I have a timetable like this:
rowid Day Period Teacher Room Subject
1 Tuesday 1 Peters A2 Maths
2 Monday 2 Peters A2 Maths
3 Monday 1 Ng A1 English
4 Monday 3 Peters A2 Computing
5 Tuesday 3 Ng A1 English
6 Tuesday 2 Ng A1 History
7 Wednesday 4 Smith S2 Science
8 Wednesday 3 Who S2 Science
9 Monday 4 Kent H1 Sport
10 Wednesday 2 Kent H1 Sport
11 Tuesday 4 Ng A1 History
12 Wednesday 1 Peters A2 Maths
And I want to output something like this:
Day Room Subject Teacher Period
Monday A1 English Ng 1
A2 Maths Peters 2
Computing 3
H1 Sport Kent 4
Tuesday A2 Maths Peters 1
A1 History Ng 2
English 3
History 4
Wednesday A2 Maths Peters 1
H1 Sport Kent 2
S1 Science Who 3
Smith 4
But with counts of repetitions, like this:
Day Room Subject Teacher Period
Monday A1 1 English 1 Ng 1 1
A2 2 Maths 1 Peters 2 2
Computing 1 3
H1 1 Sport 1 Kent 1 4
Tuesday A2 1 Maths 1 Peters 1 1
A1 3 History 1 Ng 3 2
English 1 3
History 1 4
Wednesday A2 1 Maths 1 Peters 1 1
H1 1 Sport 1 Kent 1 2
S1 2 Science 2 Who 1 3
Smith 1 4
The "1" counts could even appear as null/blank, that would be fine.
The SQL for my sample Timetable above is:
create table Timetable
(
Day text,
Period integer,
Teacher text,
Room text,
Subject text
)
;
insert into table values('Tuesday',1,'Peters','A2','Maths');
insert into table values('Monday',2,'Peters','A2','Maths');
insert into table values('Monday',1,'Ng','A1','English');
insert into table values('Monday',3,'Peters','A2','Computing');
insert into table values('Tuesday',3,'Ng','A1','English');
insert into table values('Tuesday',2,'Ng','A1','History');
insert into table values('Wednesday',4,'Smith','S2','Science');
insert into table values('Wednesday',3,'Who','S2','Science');
insert into table values('Monday',4,'Kent','H1','Sport');
insert into table values('Wednesday',2,'Kent','H1','Sport');
insert into table values('Tuesday',4,'Ng','A1','History');
insert into table values('Wednesday',1,'Peters','A2','Maths');
and a view that sorts the data is:
create view "Timetable Sorted"
as
select rowid, Day, Subject, Room, Teacher, Period
from Timetable
order by Day, Period
;
Thanks for any further insight you may have,
Tom
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
