Icon View Thread

The following is the text of the current message along with any replies.
Messages 1 to 8 of 8 total
Thread Incremental Row #?
Mon, Apr 20 2020 9:32 PMPermanent Link

Ian Branch

Avatar

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 Smile appreciated.
Regards,
Ian
Tue, Apr 21 2020 2:55 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

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 AMPermanent Link

Ian Branch

Avatar

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 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

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 Smiley


Roy

ps Two Ian Branches with different user ids must be confusing Smile
Tue, Apr 21 2020 4:19 PMPermanent Link

Ian Branch

Avatar

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 PMPermanent Link

Raul

Team Elevate Team Elevate

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 AMPermanent Link

Ian Branch

Avatar

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 AMPermanent Link

Roy Lambert

NLH Associates

Team Elevate Team Elevate

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
Image