Group by get the max ID

Hi everyone,

 I want to ask  what should i do.if i want to show the newest record on table .

Because i want to keep all the records of the food. Thank you

So i have grouped the name, and find the max ID .


eva wong wrote:

Hi everyone,

 I want to ask  what should i do.if i want to show the newest record on table .

Because i want to keep all the records of the food. Thank you

So i have grouped the name, and find the max ID .


Hi eva,

You can Sort By  on date attribute to get the newest record on the table.


Amreen


Amreen Shaikh wrote:

eva wong wrote:

Hi everyone,

 I want to ask  what should i do.if i want to show the newest record on table .

Because i want to keep all the records of the food. Thank you

So i have grouped the name, and find the max ID .


Hi eva,

You can Sort By  on date attribute to get the newest record on the table.


Amreen


Thanks Amreen,

But when i use sort by the other "old" record will be show as well, and 

it will have error 

so you want only latest record only?

then for that, you can use filters for getting  only the latest record.


thanks 

Amreen

Hi Eva,

Why not do the following?

  • apply Group By to Food.Name,
  • apply Group By to Amount and, 
  • use Aggregate Function Max on Food.Rewdate

The resulting aggregate will return the name, amount and Rewdate for the most recent Food of each kind.

Amreen Shaikh wrote:

so you want only latest record only?

then for that, you can use filters for getting  only the latest record.


thanks 

Amreen


I have try to show the latest record, but it' return with error .


Jorge Martins wrote:

Hi Eva,

Why not do the following?

  • apply Group By to Food.Name,
  • apply Group By to Amount and, 
  • use Aggregate Function Max on Food.Rewdate

The resulting aggregate will return the name, amount and Rewdate for the most recent Food of each kind.


Thanks Jorge,

May i ask if the data in amount is different, then i don't have to use group by right? 

Eva,

If you don't want to display the Amount, then don't use Group By. But if you do want to display the Food.Amount, using Group By will only display the one in the record with Food.Rewdate equal to Max(Food.Rewdate).

Can you explain better what you are trying to show on screen?

Jorge Martins wrote:

Eva,

If you don't want to display the Amount, then don't use Group By. But if you do want to display the Food.Amount, using Group By will only display the one in the record with Food.Rewdate equal to Max(Food.Rewdate).

Can you explain better what you are trying to show on screen?

Sorry Jorge, 

I would explain it clearly,

On the web page i would like to should the newest record(That include Name, Amount, Shop and Rewdate).

If i want to display the high light record on the table. 

First, i need to group by Food.Name


If you want just the highlighted items of that Aggregate then it's what I suggested, Group By each of the attributes you need (Food.Name, Food.Amount, Food.ShopId) and then get the Max of Food.Rewdate.

Jorge Martins wrote:

If you want just the highlighted items of that Aggregate then it's what I suggested, Group By each of the attributes you need (Food.Name, Food.Amount, Food.ShopId) and then get the Max of Food.Rewdate.

Thanks Jorge, 

I have try what you mention,

And the table field value is 

However, all the data are show

which part have i did wrong? 

Thank you 


Eva,

You have one extra Group By that is "ruining" your results: the Id attribute is, by definition, unique, which means there will be no grouping whatsoever. If you remove the Group of Id, you will get the desired result.

Jorge Martins wrote:

Eva,

You have one extra Group By that is "ruining" your results: the Id attribute is, by definition, unique, which means there will be no grouping whatsoever. If you remove the Group of Id, you will get the desired result.

Thanks Jorge

I have deleted the extra Group By

But It has a info, and after i 1-Click Publish, the blue button does not show and can't open on the web

Eva,

Those informative entries in the 1-Click Publish tab are unrelated (they are telling you that you have deleted attributes in two different Entities, but the corresponding columns have not been deleted from the database - which is the expected behaviour of the platform).

As for why it is not replacing the big 1-Click Publish green button with the Open in Browser blue button... typically it happens when your module does not have an Default Entrypoint, if I'm not mistaken... On your 1-Click Publish tab, do you see a blue button labeled OPEN IN BROWSER, with a dropdown next to it? If not, you should still be able to right-click on your Web Screen (in the Elements Tree) and select the Open in Browser option there...

Jorge Martins wrote:

Eva,

Those informative entries in the 1-Click Publish tab are unrelated (they are telling you that you have deleted attributes in two different Entities, but the corresponding columns have not been deleted from the database - which is the expected behaviour of the platform).

As for why it is not replacing the big 1-Click Publish green button with the Open in Browser blue button... typically it happens when your module does not have an Default Entrypoint, if I'm not mistaken... On your 1-Click Publish tab, do you see a blue button labeled OPEN IN BROWSER, with a dropdown next to it? If not, you should still be able to right-click on your Web Screen (in the Elements Tree) and select the Open in Browser option there...


HI yes I can open it on the web broswer

But i don't know why on the table, it's still show all the detail on the table.

I'm sorry, I was looking again at your query, and my initial suggestion is all wrong, because GROUPing BY Amount will also make many of the rows unique...

Jorge Martins wrote:

I'm sorry, I was looking again at your query, and my initial suggestion is all wrong, because GROUPing BY Amount will also make many of the rows unique...

Oh that's why

Thank you Jorge.

 is that mean i can't filter it in Aggregate ?


I'm still trying to wrap my head around what you are doing and whether it is at all possible with your data-model.

Tell me, when you click on the food, what are you expecting to see?