Jump to content
  • 0

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


Spectr85
 Share

Question

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.

Link to comment
Share on other sites

5 answers to this question

Recommended Posts

  • 0

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

Link to comment
Share on other sites

  • 0
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.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...