I've created two views and I can select from GroupedMostRecent and it seems to give the right answer (I say seems because it eyeballs right but I haven't properly tested yet)

CREATE VIEW "MostRecentSchedule" AS
SELECT
FriendsID,
MAX(AssignedDate) AS Newest
FROM Schedules
WHERE
FriendsID IS NOT NULL
 AND
PartID > 0
GROUP BY FriendsID
VERSION 1.00!


CREATE VIEW "GroupedMostRecent" AS
SELECT FriendsID, Newest, PartID FROM MostRecentSchedule M
JOIN Schedules S ON S.FriendsID = M.FriendsID AND S.AssignedDate = M.Newest
GROUP BY FriendsID
VERSION 1.00!

What I'd like to do, and my sql skills don't seem up to it, is merge into a single view.

Roy Lambert

Roy

The simplest way would be to simply declare the first SELECT as an internal sub-select in the second, I think.

I can't test this as I don't have the data-table ... but just cutting and pasting I have this:

CREATE VIEW "GroupedMostRecent" AS

SELECT
 FriendsID,
 Newest,
 PartID
FROM
(SELECT
  FriendsID,
  MAX(AssignedDate) AS Newest
  FROM Schedules
  WHERE
  FriendsID IS NOT NULL
  AND
 PartID > 0
 GROUP BY FriendsID) as M
LEFT JOIN Schedules S ON S.FriendsID = M.FriendsID AND S.AssignedDate = M.Newest
GROUP BY FriendsID

I may have got the wrong end of the stick.

Greetings Roy

SELECT ScheduleID, FriendsID, AssignedDate AS Newest,
    (SELECT PartID FROM Schedules S1 WHERE S.ScheduleID = S1.ScheduleID) AS NewestPart
  FROM Schedules S
  WHERE
    FriendsID IS NOT NULL AND
    PartID > 0 AND
    AssignedDate = (SELECT MAX(AssignedDate) FROM Schedules S2 WHERE S.FriendsID = S2.FriendsID)

This will only work properly if PartsID is defined as NOT NULL - you probably know this all ready.

If there are more than two Friends with the same MAX(AssignedDate), it will select both.

Isn't SQL is great - but I use to like FORTRAN, COBOL and ALGOL so that shows my age.

Richard

Richard


>This will only work properly if PartsID is defined as NOT NULL - you probably know this all ready.

Yup, but I'm not to proud to be reminded - I do forget things

>If there are more than two Friends with the same MAX(AssignedDate), it will select both.

The two GROUP BYs remove the duplicates. I know

>Isn't SQL is great - but I use to like FORTRAN, COBOL and ALGOL so that shows my age.

I can add APL & DATABASIC to that list. I had a nice "discussion" on the Embarcadero groups part of which revolved around people on there couldn't get their heads round the fact that not mangling memory used to be the norm

Roy

Adam


Thank you - it was the syntax for the sub-select delivering a table that I could not for the life of me remember, and yes - it works

Roy Lambert

Roy,

Mastering a standard keyboard is a big enough challenge, without trying to get my head around the APL keyboard.

I can see a possible issue if you have 2 Friends' row with the same MAX(AssignedDate) but different PartIDs, you cannot tell which one is really the "Newest".

SELECT  FriendsID,   Newest,   PartID < other stuff> GROUP BY FriendsID is illegal SQL (but allowable in EDB) because it may not give consistent or meaningful results.

Richard

Yey.

Really glad to have been of assistance, I know for sure I owe you for all the times you've fixed stuff for me!

And I use the Sub-select syntax A LOT now. It basically means you never really need VIEWs anymore, which I used to need for all sorts of situations.

The huge advantage of sub-selects is that the whole statement is visible in one place and you don't end up with dependencies. There are still times when a centralized VIEW, which can be accessed by a number of other SELECTs is useful, but there is also the risk of someone altering a view and generating unintended consequences.

Richard

>Mastering a standard keyboard is a big enough challenge, without trying to get my head around the APL keyboard.

Probably a major reason it diddn't catch on (although there's still versions of it out there) it meant you actually had to learn stuff and it wasn't easily human readable.

In some respects I think that was a benefit - it kept out a lot of the dodos who thought they could program because they could read

Interpreted but backed up by some heavily optimised assembler routines called by the primatives. Cadbuy's in Newcastle used APL for their scheduling / factory planning system. Every so often they would benchmark it against a FORTRAN system. APL used to win.

>I can see a possible issue if you have 2 Friends' row with the same MAX(AssignedDate) but different PartIDs, you cannot tell which one is really the "Newest".

Apparently that doesn't matter

>SELECT FriendsID, Newest, PartID < other stuff> GROUP BY FriendsID is illegal SQL (but allowable in EDB) because it may not give consistent or meaningful results.

Yup its one of the fun things about GROUP BY

Roy

Adam

>Really glad to have been of assistance, I know for sure I owe you for all the times you've fixed stuff for me!

OK only 4,125,765 paybacks to go

Roy