How to distinguish between the first and rest for duplicate records using sql ?

Question

Hi,

These are the input table and required output table.

                                  Input table 

ID  Name 
-------------
1   aaa
1   ababaa
2   bbbbbb
2   bcbcbccbc
2   bcdbcdbbbbb
3   ccccc

                                 Output table 

ID  Name        Ord
-----------------------------
1   aaa         first
1   ababaa      rest    
2   bbbbbb      first   
2   bcbcbccbc   rest    
2   bcdbcdbbbbb rest
3   ccccc       first

First and Rest is based on the occurrence of an ID field.

Is there a way to write a SQL query to achieve this ?

P.S. - This question is somewhat similar to what I am looking for.

Answer 1

select id, name, case rnk when 1 then 'first' else 'rest' end ord
from(
select *, RANK() over(partition by id order by id,name) rnk  
from input
) X

Answer 2

You can also try this

SELECT id, name,  
Decode(ROW_NUMBER() OVER (partition by id order by id,name),1,'First','Rest') Ord
FROM Input_table;

You can use this query as this is much simple and yields good performance