0

Select syntax

How can I use the SELECT command to get the most recent record with a date field ?

10replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • Ludo
    • Ludo
    • 4 yrs ago
    • Reported - view

    Like "q := SELECT Table where Date < myDate" but limit to 1 result (the most recent).

    Like
    • Support
    • Support
    • 4 yrs ago
    • Reported - view

    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

    Like
    • Laura
    • Laura
    • 4 yrs ago
    • Reported - view

    Since the most recent record would have the highest Id#, you can also do this:

    max(number(select MyTable where Date < myDate).Id)

    Like
    • Ludo
    • Ludo
    • 4 yrs ago
    • Reported - view

    Ok, I'll try those. Thank you.

    Ninox is very powerful, but a comprehensive reference would be most welcome.

    Like
    • Laura
    • Laura
    • 4 yrs ago
    • Reported - view

    Agreed!

    Like
    • Ludo
    • Ludo
    • 4 yrs ago
    • Reported - view

    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 !

    Like
    • Support
    • Support
    • 4 yrs ago
    • Reported - view

    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

    Like
  • Hi Ludo

    ---

    let lastDATE:=max(select 'Table'.'Date Field');

    'Field to copy':=last(select 'Table' ['Date Field'= lastDATE]).'Field to copy';

    ---

    Leo

    Like
    • Laura
    • Laura
    • 4 yrs ago
    • Reported - view

    Elegant, Jörg! Very helpful. Thanks for the tip.

    Like
  • 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!

    Like
Like Follow
  • 3 yrs agoLast active
  • 10Replies
  • 9580Views