0

Select top 10 records from view

Hi Guys,

I am creating a new page within Ninox to show stats from all my different views.

An example is I want to select the top 10 records from my view 'Web Support' based on the number value 'Monthyl Website Maintenance Fee (£)'

Is below the best way to achieve this? I can't find another way to limit to only 10 rows, this feels very heavy on the DB as there could be 100 records its returning first.

let topRecords := ((select 'Website Support') order by 'Monthyl Website Maintenance Fee (£)');
let cntofSelect := count(topRecords);
slice(topRecords, cntofSelect - 10, cntofSelect)

Any help be greatly appreciated

8 replies

null
    • szormpas
    • 13 days ago
    • Reported - view

     Hi,

    Pagination might be a good solution for you. Have a look at the View Widget.

    • Fred
    • 13 days ago
    • Reported - view

    That is a very good question. The only other way would be if you can get the records you need in Website Support through a reference field. Can you post a picture of your data model?

    In my DBs (you can see a recent data model), since all of my data is in the League table, I can create a dynamic choice field where I can select the League I need then do a record() and then I have access to all of the data I need flowing from there.

    Something Nioxus is doing that is interesting is they are creating a dashboard at the root of their main data tables. For example Website Support would have been created with only 1 record in it. Then you would have a Website Support Data child table with all of your records and all linked to the 1 parent record. Then you can just do a record('Website Support', 1) and you get all of the records in the child as well without doing a select. Is that really "faster" for Ninox? Maybe someone more knowledgeable can chime in.

    Not an answer, but food for thought. I look forward to responses from smarter people.

    • david.6
    • 12 days ago
    • Reported - view

    Thanks both, I'm not sure the view widget will work for me in this situation... I am showing graphs and bar charts and I have got it all to work ( see attached screenshots) .

    The below is 2 graphs of the top 10 rows from 2 different tables, using the same method above... i just worry if I had 10 more graphs of data it will be quite intensive :( 

     

    • John_Halls
    • 12 days ago
    • Reported - view

    I have created a database with a table of values, plus a page called Top n. The initialise button sets a relationship for the Top n values. This only needs to be one once.

    let a := this;
'Table of values'.('Top n' := 0);
let c := ((select 'Table of values') order by -Number);
for d from 0 to 'N Value' do
    item(c, d).('Top n' := a)
end

    After that a Trigger after update in the Table of values keeps the Top n up-to-date

    let a := this;
let b := first(select 'Top n');
let c := first(b.'Table of values' order by Number);
let d := c.Number;
if Number > d then
    c.('Top n' := 0);
    a.('Top n' := b)
end

    Regards John

    Top n.ninox
    • david.6
    • 11 days ago
    • Reported - view

    Hi John,

    Could you explain your answer in abit more detail? It would be hard for me to save details into another table as everything is on the fly and its a dashboard of graphs I am trying to create.

    For example my view contains

    - Last 10 websites launched (based on a date field)
    - Top 10 website support clients (ordered by a numeric field)
    - Project profitability (which is creating by looking at a sub table of time log and working out how much its cost)

    Apologies if thats vague

      • John_Halls
      • 10 days ago
      • Reported - view

       After the initial setup, just once, everything is on the fly.

      My model has a page with a relationship to the main table to hold the Top n records. Any changes to the value being ranked are maintained by a trigger after update that checks to see if the new value should be included and, if so it swaps out the smallest value for this new one.

      All of your use cases sound like they would work using this method ,including the graphs.

      I've updated the Trigger after update to

      let a := this;
let b := first(select 'Top n');
let c := first(b.'Table of values' order by Number);
switch true do
case 'Top n' = null and a.Number > c.Number:
    (
        c.('Top n' := 0);
        a.('Top n' := b)
    )
case count(b.'Table of values') < b.'N Value' or 'Top n' != null and a.Number <= c.Number:
    (
        b.'Table of values'.('Top n' := 0);
        let d := ((select 'Table of values') order by -Number);
        for e from 0 to b.'N Value' do
            item(d, e).('Top n' := b)
        end
    )
end

      There's probably more work to do to optimise this fully.

      Regards John

      • szormpas
      • 10 days ago
      • Reported - view

         Hi,

      Good thinking! You can dynamically assign the relationship via scripting, which is pretty clever. While you can't avoid a select statement, at least this is only called once after a new table record is added.

      If the users want to keep the link between the page and the table for all the records (e.g. for some other stats), we can get the top 10 records on a page with this code:

      let x := rsort(Table.Number);
let y := slice(x, 0, 10);
Table[contains(y, Number)]
      Top_n_records.ninox
      • szormpas
      • 10 days ago
      • Reported - view

        or, even more straightforward:

      slice(Table order by -Number, 0, 10)

      So, I'm wondering how having hundreds of records in multiple tables linked to one page (Dashboard) affects the speed of that page loading.

      From what I've seen, it's pretty much negligible, but I'd love to hear your thoughts.

Login to reply

Content aside

  • 10 days agoLast active
  • 8Replies
  • 116Views
  • 4 Following