Aggregate Function - First nvarchar in custom order

Question

I have a table with the following data:

TableColumnsId  AssumedDataType     Ranking
43264           System_String       1
43265           System_Int32        2
43265           System_Double       10
43266           System_Double       10
43266           System_Int32        2
43267           System_Int32        2
43267           System_Double       10
43268           System_Double       9
43268           System_Int32        8
43268           System_String       9
43269           System_String       9
43269           System_Int32        7
43269           System_Double       10
43270           System_String       5
43271           System_String       1
43272           System_String       8
43272           System_Double       4

I want to return only one column per TableColumnsId and the summary AssumedDataType should be the one with the lower rank.

Therefore the above data should become:

TableColumnsId  AssumedDataType     Ranking
43264           System_String       1
43265           System_Int32        2
43266           System_Int32        2
43267           System_Int32        2
43268           System_Int32        8
43269           System_Int32        7
43270           System_String       5
43271           System_String       1
43272           System_Double       4

If I use the min aggregate function with a group by it returns the first AssumedDataType alphabetically which won't work.

Is the "higher rank" the min value of ranking?, because your desired result is inconsistent. For instance, you chose the row with ranking 2 for Id 43265 (when there was another with ranking 10), but you chose the row with ranking 10 for Id 43266 (when there was another with ranking 2). — Lamak, Sep 10 '13 at 13:49

Gordon Linoff · Accepted Answer · 2013-09-10T20:05:22.003

4

You want to use row_number():

select TableColumnsId, AssumedDataType, Ranking
from (select t.*, row_number() over (partition by TableColumnsId order by Ranking) as seqnum
      from t
     ) t
where seqnum = 1;

The row_number() function assigns a sequential number to each group (defined by the partition by clause). The ordering within the group is determined by the order by clause. So, this construct assigns "1" to the row with the highest ranking for each TableColumnsId -- and the where clause chooses this value.

edited Sep 10 '13 at 20:05

answered Sep 10 '13 at 13:46

Gordon Linoff

1,242,037
58
646
786

You need to remove the desc on Ranking. The lower ranking is better. It won't let me edit because it is less than a 6 char change. – Rafi Sep 10 '13 at 13:56
@Rafi . . . I made the change. I do want to point out that the code says "the summary AssumedDataType should be the one with the higher rank", which is why I used `desc`. – Gordon Linoff Sep 10 '13 at 20:06

Sonam · Answer 2 · 2013-09-10T13:56:48.403

0

Here I'm selecting ColumnIds with the highest rank, then joining it with the main table to get DataType for that columnId and rank:

with Rank_cte(ColumnId,Ranking)
as(Select TableColumnsId,Min(Ranking) from TableName Group By TableColumnID)

Select A.TableColumnsId,AssumedDataType,A.Ranking From TableName A join 
Rank_cte B on A.TableColumnsId=B.ColumnId and A.Ranking=B.Ranking

edited Sep 10 '13 at 13:56

answered Sep 10 '13 at 13:50

Sonam

3,406
1
12
24

score 0 · Answer 3 · answered Sep 10 '13 at 13:57

0

Try this:

SELECT TableColumnsId, AssumedDataType, Ranking
FROM T t1
WHERE Ranking =
    (SELECT MIN(Ranking)
    FROM T t2
    WHERE t1.TableColumnsId = t2.TableColumnsId);

answered Sep 10 '13 at 13:57

nvogel

24,981
1
44
82

Aggregate Function - First nvarchar in custom order

3 Answers3