Return longest string if key is duplicated

user_10012449 0
Viewed 130

I have data like:

   key   value
   k1    string1
   k2    Other string 
   k3    Long string 
   k1    
   k3    string

Some of them could be empty.

I want to return longest value if key is duplicated. So the result should be like:

   key   value
   k1    string1
   k2    Other string 
   k3    Long string

My code (with solving memo field aggregating):

"IIF(COUNT(key)=1, " & _
                    "MAX(IIF(LEN(value)>250, " & _
                                          "'[CUT]' & LEFT(value, 245), " & _
                                            value & "))," & _
                    "MAX(IIF(LEN(value)>250, " & _
                                            "'[[CUT]' & LEFT(value, 245) & ']', " & _
                                            "'[' & value & ']' ))) AS " & value

The thing is that I want to return longest string from duplicates. Now I use MAX function but this return value due to alphabetical order so if value is empty it returns empty.

I am getting data using ADODB with 

[Excel 12.0;Provider=Microsoft.ACE.OLEDB.12.0;Mode=Read;Extended Properties=IMEX=1;TypeGuessRows=1;ImportMixedTypes=Text'HDR=YES;']
vba excel sql
3 Answers

You can do something like this:

select t.*
from t
where len(t.value) = (select max(len(t2.value))
                      from t t2
                      where t2.key = t.key
                     );
user_11440350

I offer this answer which can deal with multiple my_value values of the same length.

This resolves the issue in an arbitrary way using MAX and assumes all values of the MAX length are of equal fitness.

As pointed out in the comments by @TheImpaler this step may be necessary depending on your data set and need.

SELECT CAST('k1' as varchar(2)) as my_key, CAST('string1' as varchar(100)) as my_value INTO my_table;

INSERT INTO my_table VALUES('k1', NULL);
INSERT INTO my_table VALUES('k2', 'Other string'); 
INSERT INTO my_table VALUES('k2', 'sneak string'); 
INSERT INTO my_table VALUES('k2', NULL);
INSERT INTO my_table VALUES('k3', 'Long string');
INSERT INTO my_table VALUES('k3', 'string');
INSERT INTO my_table VALUES('k3', 'sneaky');

WITH s1
as
(
SELECT t.my_key
      ,COUNT(DISTINCT t.my_value) as my_value_options_total_count
      ,MAX(LEN(t.my_value)) as my_value_max_len  
  FROM my_table t
 WHERE t.my_value IS NOT NULL
GROUP BY t.my_key
)
SELECT t.my_key
     ,s1.my_value_options_total_count
     ,COUNT(DISTINCT t.my_value) as my_value_options_max_len_count
     ,MAX(t.my_value) as my_value_resolved
  FROM my_table t INNER JOIN s1
    ON t.my_key = s1.my_key
   AND LEN(t.my_value) = s1.my_value_max_len
 GROUP BY t.my_key,s1.my_value_options_total_count
 ORDER BY t.my_key;

This article has more on Common Table Expressions.

Here are the results...

+----+--------+------------------------------+--------------------------------+-------------------+
|    | my_key | my_value_options_total_count | my_value_options_max_len_count | my_value_resolved |
+----+--------+------------------------------+--------------------------------+-------------------+
|  1 | k1     |                            1 |                              1 | string1           |
|  2 | k2     |                            2 |                              2 | sneak string      |
|  3 | k3     |                            3 |                              1 | Long string       |
+----+--------+------------------------------+--------------------------------+-------------------+

I created the ASCII table using this tool. Hope this helps get you started.

user_9947710

Building on Gordon's solution I added an outer group by to deal with duplicate strings of the same length:

select id,min(val) val from my_table t
where len(val)=(
  select max(len(t2.val))
  from my_table t2
  where t2.id= t.id
)
and len(val)>0 
group by id

You can find a working demo here: https://rextester.com/KTSF40897

user_26100610
Related