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
The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.
Announcing a new way to share your feedback with the Power Automate Team.
Learn to digitize and optimize business processes and connect all your applications to share data in real time.
User | Count |
---|---|
66 | |
24 | |
16 | |
15 | |
11 |
User | Count |
---|---|
115 | |
35 | |
30 | |
28 | |
26 |