Get Top 10 values

Hi,

My app registers each user that uses it, generating a visit row (in each login or app startup), with it’s information. My client want to get the top 10 users who uses the app.

After I use a count function in a app table, ¿How can I filter or sort or get the top X rows with the highest count?

I’m using a where, a count function and group by:

queryBuilder = Backendless.DataQueryBuilder.create().setWhereClause(type = 'visit');
queryBuilder.setProperties(“Count(UserId)”);
queryBuilder.setGroupBy(“catalog”);

Thanks

Hi Dario,

To sort objects in the response, you can use the setSortBy method in DataQueryBuilder. To get the top 10 objects, you can limit the response size by using the setPageSize( 10 ) method.

Regards,
Mark

Thank you Mark,

I thought I couldn’t use the Count function inside the setSortBy

The count function returns the number of objects matching the condition. I do not quite understand how you can combine count and objects. Seems like these are two different things.

Hi Mark
Assuming that I have 20 users, I want to know the top 5 users that more uses my App. Since every login in the app creates a registry in a table I want to count this registries grouping by user and ordering by the highest counts first.

If I have this result using count and groupby functions:
user1=3 visits
user2=1 visits
user3=30 visits
user4=17 visits
user5=2 visits
user6=5 visits
user7=20 visits
user…

I want to obtain as a final result:

user3=30 visits
user7=20 visits
user4=17 visits
user6=5 visits
user1=3 visits

Hello @Dario_Castaneda

It appears you are almost there:

queryBuilder = Backendless.DataQueryBuilder.create().setWhereClause("type = 'visit'");
queryBuilder.setProperties("Count(UserId) as visitsCount");
queryBuilder.setGroupBy("UserId");
queryBuilder.setSortBy("visitsCount DESC");

Regards Vlad

Thanks @vladimir-upirov!

@vladimir-upirov another question,

How can I get the count of visits grouped by day?

If I try to group by date using created field, it gives me a wrong data, (yes, it’s a little bit obvious), but, Which date functions can I use to get the date (day, month and year) and group it by this result?

Thanks

Hello @Dario_Castaneda

Try this:

queryBuilder.setGroupBy(“DATE(created)”);

Thank you so much @Vladimir_Yalovy

Thank you so much @Vladimir_Yalovy

Thank you for contacting us. Please let us know if you run into any other issues, we’re here to make sure you have a fantastic experience with Backendless.

1 Like