Hello Community.
I call a SQL view and with the help of parse JSON and append to array variable i get this output in a create html table and then iemail it.
Name TidPunkt Conductivity Volume
0641 2020-12-01T00:00:00 0.46125364303588867 39300
0641 2020-12-01T00:00:00 0.46125364303588867 39330
0673 2020-12-01T00:00:00 0.5084977149963379 203120
0673 2020-12-01T00:00:00 0.5084977149963379 203780
0628 2020-12-01T00:00:00 0.5313577651977539 12640
0628 2020-12-01T00:00:00 0.5313577651977539 12670
0685 2020-12-01T00:00:00 1.2049660682678223
0643 2020-12-01T00:00:00 0.5157370567321777 280380
0643 2020-12-01T00:00:00 0.5157370567321777 282440
0629 2020-12-01T00:00:00 1.7265548706054688
0638 2020-12-01T00:00:00 0.42620182037353516
0614 2020-12-01T00:00:00 1.664452075958252 0
0614 2020-12-01T00:00:00 1.664452075958252 0
I would like to sort this list so i dont get more then 1 result from each name. As you can see in this list i get two records from 0641 but i only want 1 record from this name. I do understand the value is different but i dont care about that. i just want to show the the records from the same name once for easy overview.
here is my flow
/Emil2
Solved! Go to Solution.
Hi @Emil2
You can do the following:
Assuming the above query is given to you as a view then you can write the following on top of the view.
Let's say your view name is 'test' which return the above output, then you can add as below:
with cte as (
SELECT *,
ROW_NUMBER() over (
Partition by Name
Order by TidPunkt
) as rn
FROM
test
)
select * from cte where rn = 1
This will give you the desired result. If you like to understand this further then let me know, i will explain what is done by this query.
You can see a live example based on your sample data here
http://sqlfiddle.com/#!18/310b9/2
Please mark this as answer if it solves your problem.
Hi,
You can tweak the above query to get the value as per your logic.
Currently I am ordering by "so change it and order by TidPunkt desc and it should bring the latest one
check the example here
Can you put a distinct clause on your SQL Query?
Yes as suggested by @Paulie78, try to limit it at SQL level, however, distinct won't help as the values for the columns are different. You need to partition it by name and get 1 row from each group. I am not sure if this is what you can do yourself. Ping back if you need further infor.
Please see this thread for more info: https://stackoverflow.com/questions/6841605/get-top-1-row-of-each-group
Thank you but SQL its not my strong side. I do have a view i can access with this query
WITH X AS (SELECT EwonId, Name, CAST(TagDate AS DATE) AS Datum, MAX(TagDate) AS TidPunkt
FROM Talk2M.FlatTag WITH (NOLOCK)
WHERE (Name IN ('TankConductivity')) AND (TagDate >= CAST(DATEADD(dd, - 2, CAST(GETDATE() AS DATE)) AS VARCHAR(50))) AND (TagDate < CAST(CAST(GETDATE() AS DATE) AS VARCHAR(50)))
GROUP BY EwonId, Name, CAST(TagDate AS DATE)), Y AS
(SELECT EwonId, Name, CAST(TagDate AS DATE) AS Datum, MAX(TagDate) AS TidPunkt
FROM Talk2M.FlatTag AS FlatTag_1 WITH (NOLOCK)
WHERE (Name IN ('TotalPermeateVolume')) AND (TagDate >= CAST(DATEADD(dd, - 2, CAST(GETDATE() AS DATE)) AS VARCHAR(50))) AND (TagDate < CAST(CAST(GETDATE() AS DATE) AS VARCHAR(50)))
GROUP BY EwonId, Name, CAST(TagDate AS DATE))
SELECT FE.EwonName, CAST(Con.TidPunkt AS DATE) AS TidPunkt, Con.TankConductivity, Vol.TotalPermeateVolume, CASE WHEN CAST(REPLACE(Con.TankConductivity, ',', '.') AS FLOAT) > 2.5 THEN 'YES' ELSE '' END AS Alarm
FROM (SELECT EwonId
FROM X AS X_2
UNION
SELECT EwonId
FROM Y AS Y_2) AS DistRow INNER JOIN
(SELECT DISTINCT EwonId, EwonName
FROM Talk2M.FlatEwon WITH (NOLOCK)) AS FE ON DistRow.EwonId = FE.EwonId LEFT OUTER JOIN
(SELECT X_1.EwonId, X_1.TidPunkt, FT.Value AS TankConductivity
FROM Talk2M.FlatTag AS FT INNER JOIN
X AS X_1 ON FT.EwonId = X_1.EwonId AND X_1.Name = FT.Name AND FT.TagDate = X_1.TidPunkt) AS Con ON DistRow.EwonId = Con.EwonId LEFT OUTER JOIN
(SELECT Y_1.EwonId, Y_1.TidPunkt, FT.Value AS TotalPermeateVolume
FROM Talk2M.FlatTag AS FT INNER JOIN
Y AS Y_1 ON FT.EwonId = Y_1.EwonId AND Y_1.Name = FT.Name AND FT.TagDate = Y_1.TidPunkt) AS Vol ON DistRow.EwonId = Vol.EwonId
But its above my skill to understand whats going on so i hoped to go a easier way in flow 🙂
I did read about the
select top 1 with ties
But not sure how to put that in there.
If you can help im thankfull.
Hi @Emil2
You can do the following:
Assuming the above query is given to you as a view then you can write the following on top of the view.
Let's say your view name is 'test' which return the above output, then you can add as below:
with cte as (
SELECT *,
ROW_NUMBER() over (
Partition by Name
Order by TidPunkt
) as rn
FROM
test
)
select * from cte where rn = 1
This will give you the desired result. If you like to understand this further then let me know, i will explain what is done by this query.
You can see a live example based on your sample data here
http://sqlfiddle.com/#!18/310b9/2
Please mark this as answer if it solves your problem.
Thank you very much. it works as a charm.
Found one issue 😞 It only shows the oldest data. For example if i look for all data 6 days back it shows all the data from the 6 day back and not the other 5 days. How do i do so it show the "newest"?
Hi,
You can tweak the above query to get the value as per your logic.
Currently I am ordering by "so change it and order by TidPunkt desc and it should bring the latest one
check the example here
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
Learn from the top Power BI, Power Apps, Power Automate & Power Virtual Agents experts!
User | Count |
---|---|
33 | |
32 | |
23 | |
22 | |
19 |
User | Count |
---|---|
58 | |
55 | |
41 | |
37 | |
28 |