I have ms sql table like following.

I want to get distinct rows by num1 & num2 with highest range. Query should return result like this.

Num1 & Num2 values are grouped (please note that 2,1 and 1,2 are considered as same). If the ere are multiple 1,2 rows with the highest range is selected from that group.

So far I could return following but it contains Num1 & Num2 swapped rows (1,2 & 2,1...).

Query should return the column as in the 1st table and Num1&Num2 data cannot be swapped.

Query I've tried

Select Max([ID]) As 'ID'
      ,Max([Num1]) As 'Num1'
      ,Max([Num2]) As 'Num2'
      ,Max([Range]) As 'Range'
    From [dbo].[Range]
    Group by Concat([Num1], [Num2])
    Order By Range desc

CodePudding user response：

We can use a least/greatest trick here along with aggregation. Actually, we can't do that on SQL Server, which doesn't support these scalar functions, but we can still use CASE expressions:

WITH cte AS (
    SELECT *, MAX([Range]) OVER (PARTITION BY 
                  CASE WHEN Num1 < Num2 THEN Num1 ELSE Num2 END,
                  CASE WHEN Num1 < Num2 THEN Num2 ELSE Num1 END) AS MaxRange
    FROM yourTable
),
cte2 AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY 
                  CASE WHEN Num1 < Num2 THEN Num1 ELSE Num2 END,
                  CASE WHEN Num1 < Num2 THEN Num2 ELSE Num1 END
                  ORDER BY MaxRange DESC) rn
    FROM cte
)

SELECT ID, Num1, Num2, [Range]
FROM cte2
WHERE rn = 1
ORDER BY ID;

CodePudding user response：

Provided you're using SQLServer 2017 you can use string_agg via an apply to create the condensed groupings required (this would easily support more than 2 columns if required) and a row_number solution for ordering by range:

with g as (
  select *, Row_Number() over(partition by grp order by range desc)rn
  from t
  cross apply (
    select String_Agg(n, '') within group(order by n) 
    from (values(num1), (num2))v(n) 
  )s(grp)
)
select Id, num1, num2, Range
from g
where rn = 1
order by id;

See Demo Fiddle

Edit:

on SQL server 2014 you could substitute for the for xml path hack:

with g as (
  select *, Row_Number() over(partition by grp order by range desc)rn
  from t
  cross apply (
    select top(2) n
    from (values(num1), (num2))v(n) 
    order by n
    for xml path('')
  )s(grp)
)
select Id, num1, num2, Range
from g
where rn = 1
order by id;