How to select first and two last records in table for making DataPage

[Spectr85]

Hi, guys! I need some help.

I have a table with fields: autonumber (unique), userName, userNumber (11, 22, 33 etc.), timestamp, value.

for each user (11, 22, 33)  there is several records with different timestamp and values.

How can I select in table first and 2 last records for user ? I make a DataPage Report with type Gallery and have to compare first and last two values, sorted by timestamp field.

UserNumber is entered use the search form.

[MayMusic]

To your report DataPage add 2 calculated fields and use the formulas below. Replace TABLE NAME with the actual table name

 

To get the top 1 record you can have:

SELECT TOP 1 value FROM [TABLE NAME] WHERE userNumber =target.[@field:userNumber ] ORDER BY timestamp DESC

To get the second last

SELECT TOP 1 value FROM
(SELECT TOP 2 value, Date FROM [TABKE NAME] WHERE  userNumber =target.[@field:userNumber ] ORDER BY timestamp  DESC) AS T ORDER BY timestamp  ASC

 

Best would be having two tables:

One that has user info like UserID (Unique), Name ...

And another table that has User ID, Vaue, Timestamp

 

Create your report based on user table so you do not get duplicates for each user in your report.   Then in the formulas above read the values from the second (child) table

[Spectr85]

On 11/1/2018 at 4:43 PM, MayMusic said:

To your report DataPage add 2 calculated fields and use the formulas below. Replace TABLE NAME with the actual table name

.....

MayMusic, thank you so much!

I din not know, that I can use SQL queries with Caspio engine.

On your SQL queriy I'll get the value1 of the value fields in the 1st and two last lines.
This is great, I'll know how it works!

 

But I meant a little bit different. I try to explain:

In fact, I have about 25 values in the table: value1, value2, ... value25. Based on this the chart and the coefficients are calculated (I have 12 hidden calculated fields, which used all values from v1 to v25).

I meant, as in the stage "Choose the fields you need to use in filtering data:"  how  can I leave only the first and last two lines.

By receiving the userName field, I get a summary table, for example in 10 rows with entries only for this client. How from this table to leave the first and two last lines.  Entries are sorted by  datastamp field.

Of course if it possible.

[MayMusic]

You cannot show two lined only. That is why I recommended you to have two tables

[Spectr85]

11 hours ago, MayMusic said:

You cannot show two lined only. That is why I recommended you to have two tables 

OK, I understood.

MayMusic, thank you for information. I try to rebuild my table, like you adviced.

[MayMusic]

Sounds good let me know how it goes ...