Group by MAX

Wappler General How To
#1

group by MAX doesn’t work great ! why ? @Teodor

image
image1466×845 68.7 KB

in mysql this code works great:

SELECT *, MAX(id_user_ricezione)
FROM messaggi
GROUP BY id_user_ricezione

i want replay this code in Database Query Builder

#2

Hi,

I want to offer a few thoughts to you even though I’m not very knowledgeable in these things - so please take my response with a grain of salt.

  1. In the future, it will be much easier to get help from this great forum when you explain a bit more about the problem. In this case, adding information on what you expect to obtain vs. what you actually obtain when running the query will go a long way.

  2. From what I see, the query you built in Wappler is rather different from the example of the query you want. In the former, you have INNER JOINs and WHERE clauses that do not appear in the latter, so depending on your data, it is very reasonable to expect that the results will be rather different.

  3. A quick way to test in Wappler the query that you know is working well is to use a custom query step. Just copy the 3 lines in your query there and try to run it. The front end to configure a custom query step has a button at the top that allows you to run the query and see the results. It’s a great tool to explore what’s going on without having to run the whole server action API.

Hope this helps you. Happy New Year!

Alex

1 Like
#3

When using the Aggregates you should give it an Alias.

#4

I can’t replicate this SQL function in Database Query Builder
Help me ! Please @Teodor @patrick

image
image1058×515 20.1 KB

I have this but it doesn’t work great !

image
image1100×646 41.9 KB

#6

Did you try to recreate this with a custom query? Sometimes this helps.

#7

ok, but if I only select the max how can I retrieve the other fields?
or manually edited the API file and it works, but with query builder it doesn’t

#8

Look, since it is already working as an SQL query, try recreating your Query using ‘Custom query’ builder within Wappler.

#9

SELECT *, MAX(id_user_ricezione)
FROM messaggi
GROUP BY id_user_ricezione

Recreate this using Custom Sql Query within Wappler.

#10

ok, it works but I need the identity condition and :P1 doesn’t work

#11

I think you should create it as a variable the :P1 inside custom query builder. Did you do create it? Also if :p1 does not work try filtering with :p2 (but also create the variable in the query builder)