Display first and last entry from SQL Database based on one column filter

[FilipC2]

Hello Community,

I have below SQL Database in place which is updated few times per day. List contains thousands of rows:

I am looking for a way to filter the Counter of the list and display in one label the first tag_value_human of tag_shift 1, another label to display last tag_value_human of tag_shift 1 and same for tag_shift 2, 3.

Taking second image as example: First Counter would be 375814 and Last Counter would be 375834.

Thank you in advance for your support !

BR

[subsguts]

I set up a collection, similar to your data to show the solution I think will work for you.  

Here's the collection:

Collect(colExample, Table({ tag_name_human: "Counter", tag_value_human: 1234},
{ tag_name_human: "Speed", tag_value_human: 5687},
{ tag_name_human: "Counter", tag_value_human: 6733},
{ tag_name_human: "Speed", tag_value_human: 8888},
{ tag_name_human: "Counter", tag_value_human: 9853},
{ tag_name_human: "Counter", tag_value_human: 7333}))

To get the First value:

First(Filter(colExample,tag_name_human="Counter")).tag_value_human

Note: Returns 1234

To get the Last value:

Last(Filter(colExample,tag_name_human="Counter")).tag_value_human

Note: Returns: 7333

Let me know if this helps?

[FilipC2]

@subsguts,

This is not helping me since, as mentioned in initial post, i also have to filter the tag_shift column.

It's not like i have separate tables per shift. It's same one, only that i filtered the database to not show all useless big database in the post ... 

So, taking the above picture as example I need to get displayed in labels, first tag_value_human of tag_shift 1, another label to display last tag_value_human of tag_shift 1, first tag_value_human of tag_shift 2, another label to display last tag_value_human of tag_shift 2, first tag_value_human of tag_shift 3 and another label to display last tag_value_human of tag_shift 3.

[subsguts · Accepted solution]

Just change the filter to include the tag_shift.  Here are the two for shift 1.  Then just repeat changing the tag_shift value from a 1 to a 2 and to a 3.  That will give you all 6 labels.

Label1.Text property ("first tag_value_human of tag_shift 1"):

First(Filter(colExample,tag_name_human="Counter" && tag_shift=1)).tag_value_human

Label2.Text property ("last tag_value_human of tag_shift 1"):

Last(Filter(colExample,tag_name_human="Counter" && tag_shift=1)).tag_value_human

Label3.Text property

Hope this works, but if not let me know.

[FilipC2]

@subsguts ,

Even if I was using different ways of filtering, I couldn't get it why I was not retrieving the desired values from my database and now after I also tried your method that is also working very well since I am getting First() value but still not getting the Last() desired one, I reminded that PowerApps have the data row limit able to read from a maximum 2000 item lines and my Database is constantly adding lines and it's getting to hundreds of thousands of lines.

I guess I will have to look after more filtering to get desired values.

Ty for supporting !

[subsguts]

If you are working with a large dataset then you'll have to determine a way to reduce it down slowly by nesting your filter statements.  Will you have < 2000 entries per "tag_shift"?  If so, then you can nest as follows to get your last value.  This will filter by tag_shift first and as long as that returns less than 2000 records the filter will work.

Last(Filter(Filter(colExample,tag_shift=1),tag_name_human="Counter")).tag_value_human

However, if there will be greater than 2000 entries per tag_shift and there is no other way to reduce to under 2000 then you'll have to use a collection and do a series of statements to populate a collection.  After the collection is populated then you'll be able to use it as your data source without the 2000 record limitation.

There are other posts and blogs, etc. on working with large datasets that explain how to build this collection if this ends up being what you need.

Feel free to reach out to me with questions.  Hope this is helpful.

[FilipC2]

Many thanks for your continuously support!

Unfortunately, I am working with I can say massive data sets. My table is gathering around 150k items / day, divided by 3 shifts, I have around 50k entries / shift.

If you could help in any way with building the collection and with the series of statements would be really appreciated. I will also try to document this.