CODEWITHSUNDEEP
sqloracle-database
bharath
bharath

Reputation: 49

How to get rank for each row with group by in oracle sql

I have table like below

Col_1   Col_2   Comment
1       ABC     test
1       ABC     test1
1       ABC     test2
2       DEF     xtest
2       DEF     xtest1
3       XYZ     ytest
3       XYZ     ytest1
3       XYZ     ytest2

And the result I want is

1       ABC     test | test1
2       DEF     xtest | xtest1
3       XYZ     ytest | ytest1

So I want to group by Col_1 and Col_2 and aggregate only the first 2 values of Comment column.

Any idea?

Upvotes: 0

Views: 935

Answers (2)

Srini V
Srini V

Reputation: 11375

Oracle 11g Release 2 supports the LISTAGG function.

SELECT col_1, 
       col_2, 
       Listagg(COMMENT, '|') 
         within GROUP (ORDER BY COMMENT) AS new_comment 
FROM   (SELECT *, 
               Row_number() 
                 over ( 
                   PARTITION BY col_1, col_2 
                   ORDER BY COMMENT) AS n 
        FROM   yourtable) 
WHERE  n <= 2 
GROUP  BY col_1, 
          col_2;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271013

You can get two values using min() and max():

select col_1, col_2, min(comment) || ' | ' || max(comment)
from t
group by col_1, col_2;

SQL tables represent unordered sets, so there is no such thing as "the first two comments".

Upvotes: 0

Related Questions