CODEWITHSUNDEEP
sqldatabasedb2
Vijay Kasina
Vijay Kasina

Reputation: 144

DB2:how to get top

I have a table having data like

pin   id      name
3     33      jjj
2     22      bbb
1     111     aaaa
1     112     aa
1     113     aaa
4     44      kkk

I want to print rows of the table where if count(*) group by pin =1 (i.e single entry in table ) print the row

if count(*) group by pin >2 then print first two rows

so my out put should be

pin   id      name
3     33      jjj
2     22      bbb
1     111     aaaa
1     112     aa
4     44      kkk

Upvotes: 0

Views: 76

Answers (1)

PeterRing
PeterRing

Reputation: 1797

Use row_number() OVER(partion by pin order by id) as rownum function . Where rownum <3 . As @Clockwork-Muse said, you need to define an order becase you need to say what do you want to see if there are more than 2 rows for a particular pin. This will generate you desired output.

Upvotes: 1

Related Questions