Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 12 total |
Combine two views |
Sat, Nov 18 2017 3:12 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sat, Nov 18 2017 4:31 PM | Permanent Link |
Adam Brett Orixa Systems | 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. |
Sat, Nov 18 2017 4:48 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | 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 |
Sun, Nov 19 2017 3:01 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sun, Nov 19 2017 3:01 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Sun, Nov 19 2017 2:16 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | 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 |
Sun, Nov 19 2017 5:06 PM | Permanent Link |
Adam Brett Orixa Systems | 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! |
Sun, Nov 19 2017 5:10 PM | Permanent Link |
Adam Brett Orixa Systems | 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. |
Mon, Nov 20 2017 2:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Mon, Nov 20 2017 2:48 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Wednesday, August 14, 2024 at 03:12 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |