Select syntax
How can I use the SELECT command to get the most recent record with a date field ?
10 replies
-
Like "q := SELECT Table where Date < myDate" but limit to 1 result (the most recent).
-
Hi, you could also use the first() function:
first(select MyTable.mydate)
That will give you the first entry of that table that has a content in the field mydate.
Best, Jörg
-
Since the most recent record would have the highest Id#, you can also do this:
max(number(select MyTable where Date < myDate).Id)
-
Ok, I'll try those. Thank you.
Ninox is very powerful, but a comprehensive reference would be most welcome.
-
Agreed!
-
Those solutions didn't work for me...
The last record may not have the most recent date.
I'm trying to copy some field content from the record with the most recent date.
I have an ugly workaround with a loop :
let myDate := 'Date Field';
let targetDate := 0;
for q in select 'Table' where number('Date Field') < myDate do
if number(q.'Date Field') > targetDate then
'Field to copy' := q.'Field to copy';
targetDate := number(q.'DateField');
end
end
There must be a more elegant solution !
-
Hi Ludo,
if you go with Lauras suggestion to max instead of first:
max(select MyTable.mydate)
you will get the most recent date.
Best Jörg
-
Hi Ludo
---
let lastDATE:=max(select 'Table'.'Date Field');
'Field to copy':=last(select 'Table' ['Date Field'= lastDATE]).'Field to copy';
---
Leo
-
Elegant, Jörg! Very helpful. Thanks for the tip.
-
Hi,
sorry to post here but i m working on a db and i dont know where to get the information that i need. I want to have a table with client information (id, name, phone number) for exemple.
I would like to have a table with all phone numbers and other table with the client linked with a phone number and with a date. So for exemple, john 514-555-5555 january 1st , john again with another number and another date....
then I would like to link the field 'phone number' in the client table to the most recent number for this client.
so if john change his number, I can have an historic of his past number... each number will be assign only once so I need to know if it s already assign but it s for the form part...
can we link field to table view? If so, how? if not, I can I do ?
Hope I explain it well!
thank you and have a nice day!
Content aside
- 5 yrs agoLast active
- 10Replies
- 9670Views