Hello,
Is there any way to implement the following SQL Query in AppSheet as a virtual column:
RANK() OVER (
PARTITION BY [Date]
ORDER BY [Created_At]
) AS Sequence
Yes, there are ways depending on what you’d like to achieve.
Literal code transcription is generally not a good practice, better explain in plain language what is your requirement for which you’d need a rank.
2 Likes
Ok, say I have a single table, Customers, with the following columns: [Date], [Customer Name], [Created At]. For brevity, I have 6 customers coming in over 2 days (3 customers per day). I would like to rank these customers on each day, by the order that they came in at.
| Date | Customer Name | Created At |
|---|---|---|
| 06/03/2022 | Customer C | 06/03/2022 08:32:56 AM |
| 06/03/2022 | Customer B | 06/03/2022 15:32:56 PM |
| 06/03/2022 | Customer A | 06/03/2022 11:32:56 AM |
| 07/03/2022 | Customer A | 07/03/2022 09:45:20 AM |
| 07/03/2022 | Customer B | 07/03/2022 17:05:46 PM |
| 07/03/2022 | Customer C | 07/03/2022 12:22:03 PM |
I would like to add a virtual column named “Sequence”, showing the rank of each customer by the time the values were created at on each day.
| Sequence | Date | Customer Name | Created At |
|---|---|---|---|
| 1 | 06/03/2022 | Customer C | 06/03/2022 08:32:56 AM |
| 3 | 06/03/2022 | Customer B | 06/03/2022 15:32:56 PM |
| 2 | 06/03/2022 | Customer A | 06/03/2022 11:32:56 AM |
| 1 | 07/03/2022 | Customer A | 07/03/2022 09:45:20 AM |
| 3 | 07/03/2022 | Customer B | 07/03/2022 17:05:46 PM |
| 2 | 07/03/2022 | Customer C | 07/03/2022 12:22:03 PM |
Thank you. I already understood this from the expression in your original post.
My question is: what do you need the rank for? What would you do with the rank afterwards? Would you please explain?
1 Like
I am using SQL Server 2019 as the backend to AppSheet.
The rank would be used in a slice, filtered using [Date] = TODAY(), so I can see the order in which the customers came in. The UX for this would be using Table type, with name: Detail View.
This is to:
-
Display to the user so they can service the customers in that order
-
Display to the user the total number of customers that came today by scrolling to the last row
AFAIK, there is no option to display a single metric in AppSheet, except to use a SQL query to summarize and use a deck / card view to display a slice filtered by today.
E.g. to obtain Customer_Count_By_Day as a single metric view:
CREATE VIEW Customer_Count_By_Day AS
SELECT
Date,
COUNT(Customers) AS Customer_Count
FROM customers
GROUP BY Date
Add this query view as a Table to AppSheet, create a filtered slice, display to user in UX as card / deck (Name: Metric View) and combine that in a Dashboard view as:
-
Metric View
-
Detail View
Where I am stuck at now is to implement the RANK() function in AppSheet, so the user can scroll through the list of customers, ordered by the time at which they were created, for the purposes outlined above.
Could you update how [Created At] column is populated?
[Created At], “DateTime” data type, Initial Formula: NOW()
Thank you. Maybe I still missed some point , however
-
Generally AppSheet adds rows in sequential order of timetamp. Any specific reason the record of later timestamp will be stored earlier ?
-
Are you not able to use sort by [Created at] on the table view of the slice you will create with [Created at]=TODAY(), which will give the sort order in [Created at]
2 Likes
Thank you..
Fortunately for this purpose you don’t need a virtual column with formulas which is always better to avoid.
You’ll just create the slice normally and link it to a view. In the view you can specify the sorting order of your linked table/slice based on any column, and in your case it will be the “Created_At” column. Also in the view, you don’t even need to show the “Created_At” column even if you are using this column for sorting the output.
The user will see customers, as required, in the order they had arrived in.
Also,
In the view, in addition to sorting, you CAN group by any column and automatically display a group metric like Count, Sum, Average, Min and Max. You should just have the required columns to group by in your table. So if you’d like to group by date on a daily basis, you should have a column “Date” with an initial value of TODAY().
Please read the guide below and tell me if you have any doubts.
Views: The Essentials | AppSheet Help Center
Personal opinion: Rank function in SQL is just an expensive futility.
3 Likes
Thanks to both for the input.
However, the display of the sequence matters to the user, as we need to refer to the customer by the order they came in at today, e.g. Customer 15 needs to be serviced.
While that can be achieved using Sort By in UX and hiding the [Created At] column, the user would not be able to see or refer to the [Sequence No] that the customers came in at for today.
To try and illustrate this with a story, suppose the user is a bank officer sitting in the branch. 10 customers came in and were registered using [Created At], clustered around 8:45AM with a tight range around the seconds.
Each of the customers are given a printed ticket without a registration number [Sequence No].
With [Created At] timestamp as the only information available, the customers would be arguing, “Hey, I came 2 seconds earlier, why am I not being serviced first?” and the user would need to compare timestamps to resolve the conflict.
1 Like
OK I understand, thank you!
Well, the Rank() function might not be an optimal solution in case for example a customer returns again after being served the first time. The second time, and later times, he would get the same sequence number as the first time.
If you would like to have a sequence that would reset on a daily basis, then a better solution is to add a “Sequence” column, normal not virtual, and put this expression in its App Formula:
COUNT( FILTER(“tableName”, TODAY() = [Date]) ) + 1
Even better, since you are already using a daily slice, the expression should be:
COUNT( FILTER(“sliceName”, TRUE) ) + 1
Rank() is a costly, capricious futility 
3 Likes
Gnerally such sequence numbering with AppSheet virtual column will be sync time expensive because it will be a multirow expression.
Anyway, you may wish to try something like below for the sequence number expression in a number type VC
If the table’s key is fixed lenghth ( 8 character ) such as UNIQUEID(), please use an expression something like
FLOOR(FIND([Key Column], SliceName[Key Column])/11)+1
If the key column length is uneven, please try an expression something like
COUNT(SPLIT(LEFT(SliceName[Key Column],FIND([Key Column],SliceName[Key Column])),” , "))
1 Like
@Joseph_Seddik : As it happens a few times with this community portal, I did not see your latest reply when I was typing in my reply. The previous portal used to to show if someone else is typing. In such cases, one could wait, especially if the other person is already responding to the thread.
In this portal, unless I manually refresh, I do not see latest posts, or am i missing some setting?
2 Likes
Hi @Joseph_Seddik : As it happens a few times with this community portal, I did not see your latest reply when I was typing in my reply. The previous portal used to to show if someone else is typing. In such cases, one could wait, especially if the other person is already responding to the thread.
In this portal, unless I manually refresh, I do not see latest posts, or am i missing some setting?
2 Likes
Masterful .. thank you for the solution
1 Like
No my friend you are not missing anything. It happened with me a lot of times ever since we moved to this pessimal portal, and it is very annoying 
2 Likes
@Suvrutt_Gurjar I have tried using your expression, but the sequence order appears to be reversed, i.e. Sequence [1, 2, 3] appears as Sequence [3, 2, 1]
Yes I am using UNIQUEID() as key and App Formula.
Sorry, should have included ORDERBY(), even though even without that typically list gets created in sequence. Anyway please try
FLOOR(FIND([Key Column], ORDERBY(SliceName[Key Column],[Date], FALSE ))/11)+1
Edit: Added one missing parenthesis
3 Likes
This was an interesting discussion thread that outlined two approaches of sequence numbers with real column and virtual column. Just for any future readers of the thread, the better points and challenges between a real column and a VC approach, as per my understanding.
Real column will calculate the sequence number when a row is being added. So it will be better in terms of overall sync time of the app as it will not be recalculated on each sync. However, if multiple user are adding the rows, sequence number may be duplicated or if a few rows are deleted after adding, the sequence number will not be recalculated for the following rows unless one uses actions or physically redits those rows.
With VC, the sequence number will dynamically adjust itself ( of course on resync of the user’s device) when rows are added or deleted. However if there are many hundreds of rows in the slice on a day, it will impact the sync time. It may not so much impact the sync time for a few 10s or few 100s of rows. Also with VC the “real” storage of sequence in the adatabase is not there, which could be needed in certain cases.
2 Likes
@Suvrutt_Gurjar Yes you are right, I have tested your formula and the sync time has made the app unusable, data contains about 10k rows
2 Likes