Fetching of duplicate record

Question

I want to fetch duplicate records from a table (n-1 records out of n records). How should such a query look?

example (table name = markslist):

Name         Marks
Janny          9
raj           10
raj           10
raj           10
rose           8

If the table structure is like this, I want to fetch raj for two times (n-1) out of 3 (n) times.

I tried using rank, but couldn't get the result I wanted.

Answer 1

First, when designing tables, it is best to have a primary key that uniquely identifies each row. If you had one, this would be simpler.

The easiest answer is to use row_number(), an ANSI standard function available in most databases:

select t.*
from (select t.*, row_number() over (partition by name order by name) as seqnum
      from markslist t
     ) t
where seqnum > 1;