Group by get the max ID
Question

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

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 .


mvp_badge
MVP

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.

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? 

mvp_badge
MVP

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


mvp_badge
MVP

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 


mvp_badge
MVP

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

mvp_badge
MVP

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.

mvp_badge
MVP

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 ?


mvp_badge
MVP

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?

eva wong

I suppose the problem is solved by now. But I don't see any answers here. So I am writing to hope and help others.

My best guess of the situation is: For each shop, for each product, you want to get the LATEST price.

If you can handle raw SQL, then write sub-query for that. For out system, the best option would be to redesign your tables: Always overwrite the old prices, and keep your history in another table.

If you can't do either, try the following:

Query all records, group by your shopID and productID, get max(rewDate).

Loop through the record, for each record, query your database again:

  • In this query, filter the records by shopID, productID, and rewDate = max(rewDate)
  • You should get only one record, this record has the latest price. add this record to a new list of record (local variable)



after a lot of research, this was the simplest solution I identified to return the last ID, thanks 

Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.