Remove unwanted data from array

[Emil2]

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

[Paulie78]

Can you put a distinct clause on your SQL Query?

[nmk]

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]

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. 

[nmk · Accepted 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.

[Emil2]

Thank you very much. it works as a charm. 

[Emil2]

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"?

[nmk · Accepted solution]

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