cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Emil2
Frequent Visitor

Remove unwanted data from array

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 

Untitled.png

 

/Emil2

2 ACCEPTED SOLUTIONS

Accepted Solutions

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.

View solution in original post

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

 

http://sqlfiddle.com/#!18/ba3e0/3

View solution in original post

7 REPLIES 7
Paulie78
Super User
Super User

Can you put a distinct clause on your SQL Query?

nmk
Resolver I
Resolver I

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

Emil2
Frequent Visitor

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.

View solution in original post

Emil2
Frequent Visitor

Thank you very much. it works as a charm. 

Emil2
Frequent Visitor

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

 

http://sqlfiddle.com/#!18/ba3e0/3

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (1,742)