MySQL Cant get Rank() function to work

[ttoorree]

I'm building a timing application and need to use the Rank() function for Mysql, but it return strange data.
Here is my query:

select 
    'table' as component,
    TRUE    as sort,
	--'Sport' as icon,
	TRUE	as hover,
	'Details' as markdown,
    TRUE    as search;
Select  Rank() OVER (order by result.elapsed_time asc) as 'Rank',
		result.bib as 'BIB',
        participant.participant_name as 'Name',
		TIME_FORMAT(result.start_datetime, "%T") as 'Start',
		TIME_FORMAT(result.finish_datetime, "%T") as 'Finish',
		result.penalty as 'Penalty',
		result.elapsed_time as 'Elapsed time',
		'[Details](participant_split.sql?id_participant=' || participant.id || ')' as 'Details'    
from result
left join participant on result.id_participant = participant.id
where result.id_race = $id_race
order by result.elapsed_time asc  

And here are the result:

With debug component, i get this data:

When i run the same query on DB, it's working fine

Am i doing something wrong?

Thanks :)

[lovasoa]

This sounds like a bug in SQLPage. I'll investigate.

in the meantime, can you try to cast the rank to text before returning it to SQLPage?

[lovasoa]

@ttoorree : I confirm this is a bug with how SQLPage handles MySQL values of type BIGINT SIGNED, such as the result of the rank function.

The bug will be fixed in the next version.

In the meantime, you can use

SELECT 
  CAST(Rank() OVER (order by result.elapsed_time asc) AS CHAR) as 'Rank',
  ...

[ttoorree]

I'm building a event/race planner and timing system using UHF RFID bib's. (For running events, but plan to expand for other sports later)

1. Organizers register their event and race with all the info a participant need.
2. Race are set up with timing equipment such as UHF RFID readers (Building myself).
3. Participants search for a event/race they want to participate in, and register.
4. After registering, they are sent to a payment platform (working on it).
5. All participant's create their own profile, so that they can se all past results and upcoming events.
6. Non participant's can also view live results during race.

This is the basic functionality, but will expand with features when the system is up and running.

[lovasoa]

That sounds really cool ! I'd love to participate to a race where I would see the results on a SQLPage website. How has your experience with the tool been so far ? Did you have trouble getting started (apart from this bigint bug) ? Do you already have a plan for hosting your app ?

[ttoorree]

I will invite you to Norway to participate in a race as soon it's up and running :)
My experience with SQLpage is very good. Easy and fast setup.
Sometimes there are some challenges, but i'm not sure it's my lack of SQL skills or a bug. Usually I get them resolved using Google and looking at your examples. Your documentation is very good.
Temporary, my plan is to host the app on my own server, but are looking for other solutions/vps.
I'm also running a MySQL server, but will switch to my domain name provider's MySQL server.
Do you host VPS?

[alexisrc1]

Hello @ttoorree,

Congratulations on what you’re building with SQLPage—it looks very promising! We have a solution to host your SQLPage application, which you can check out here: https://beta.datapage.app/. Please don’t hesitate to book a meeting with me https://cal.com/alexisrc/30min if you’d like to learn more. I’d also love to see what your app looks like, so feel free to reach out. 🙂

[lovasoa]

Sometimes there are some challenges, but i'm not sure it's my lack of SQL skills or a bug.

Don't hesitate to post here in such cases. I consider everything that doesn't work on the first try as a bug, either in SQLPage or in its documentation !

Do you host VPS?

Yes, see Alexis' response! We'd be happy to host you :)