![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 8 of 8 total |
![]() |
Mon, Apr 20 2020 9:32 PM | Permanent Link |
Ian Branch | Hi Team,
I have the following sql.. {sql} Select * from UsersLog, Users where (UsersLog.UserID = Users.UserID) and (FinishDateTime is NULL) order by StartDateTime {sql} I want to add an additional column that will reflect an incrementing row number for each UsersLog entry shown. Suggestions (polite ![]() Regards, Ian |
Tue, Apr 21 2020 2:55 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Ian
>{sql} >Select * from UsersLog, Users >where (UsersLog.UserID = Users.UserID) and (FinishDateTime is NULL) >order by StartDateTime >{sql} 1. Don't do that use a JOIN instead SELECT * FROM Users JOIN UserLog UL ON UsersLog.UserID = Users.UserID WHERE UL.FinishDateTime is NULL ORDER BY UL.StartDateTime 2. Getting a row number is possible IF you use a GROUP BY eg SELECT RUNSUM(1), * FROM Users JOIN UserLog UL ON UsersLog.UserID = Users.UserID WHERE UL.FinishDateTime is NULL ORDER BY UL.StartDateTime GROUP BY Users.UserID Roy |
Tue, Apr 21 2020 3:32 AM | Permanent Link |
Ian Branch | Hi Roy,
I don't want it grouped... It should look something like the attached, but with a Row counter as the left column. The Users table is there purely to retrieve the User's Title & Name based on the UserID. Ian Attachments: Screenshot_6.jpg |
Tue, Apr 21 2020 6:28 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Ian
>I don't want it grouped... >It should look something like the attached, but with a Row counter as the left column. >The Users table is there purely to retrieve the User's Title & Name based on the UserID. Hunting through the newsgroups there is a solution not using GROUP BY but I don't think you'd like it. There is a nifty solution given by Raul that I'd forgotten SELECT RUNSUM(1), * FROM Users JOIN UserLog UL ON UsersLog.UserID = Users.UserID WHERE UL.FinishDateTime is NULL ORDER BY UL.StartDateTime GROUP BY __RowID NOTE - two underscores __RowID is an ElevateDB maintained column, I think its the row number when inserted into the table, is guaranteed to be unique and hence grouping by this row means every row is in a group of one ![]() Roy ps Two Ian Branches with different user ids must be confusing ![]() |
Tue, Apr 21 2020 4:19 PM | Permanent Link |
Ian Branch | Hi Roy,
Again I get.."ElevateDB Error #700 An error was found in the statement at line 8 and column 1 (Expected end of expression but instead found GROUP)" in edbmgr. No, the two IDs aren't an issue. They are related to different 'businesses' in the database and I normally only test with the '01. The '02 is for when I am confirming that the two business models are acting independently. i.e. their own stock holdings. Regards, Ian |
Tue, Apr 21 2020 6:08 PM | Permanent Link |
Raul ![]() | On 4/21/2020 4:19 PM, Ian Branch wrote:
> Hi Roy, > Again I get.."ElevateDB Error #700 An error was found in the statement at line 8 and column 1 (Expected end of expression but instead found GROUP)" in edbmgr. SQL is OK bu the GROUP by needs to be before ORDER BY . i.e. SELECT RUNSUM(1), * FROM Users JOIN UserLog UL ON UsersLog.UserID = Users.UserID WHERE UL.FinishDateTime is NULL GROUP BY __RowID ORDER BY UL.StartDateTime Raul |
Wed, Apr 22 2020 2:18 AM | Permanent Link |
Ian Branch | Thanks Roy, Raul,
Got it working as desired with this.. {sql} SELECT RUNSUM(1), * FROM Users JOIN UsersLog UL ON UsersLog.UserID = Users.UserID WHERE UL.FinishDateTime is NULL GROUP BY UL.StartDateTime ORDER BY UL.StartDateTime {sql} Thanks for the assist Guys. Ian |
Wed, Apr 22 2020 3:13 AM | Permanent Link |
Roy Lambert NLH Associates ![]() | Raul
Well spotted - one of my main GOTCHAs along with SEELCT! Its always surprised me that a preprocessor wasn't part of the spec to reorganise clauses in the desired order <shrug> Roy Lambert |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |