Need help for Filtering the data in sql

i have a table that contain s.no Id and Amount and accCode.

  s.no-------------id--------------Amount--------accCode
   1----------------2---------------20-------------2.1
   2----------------1---------------30-------------2.1
   3--------------- 5---------------20-------------3.1
   4----------------1---------------30-------------2.1
   5----------------3---------------40-------------3.1
   6----------------2---------------20-------------2.1

i nee开发者_Python百科d all the record that have a common Amount and accCode and id. In this case i need to show the data of S.NO 2 and 4, and also 1 and 6 as they have similar value. If Possible it would be better the similar data come orderly. Is this one possible through Sql? Please give some hints i am stuck with this one.thaks in advance.

One solution could be, assuming that your table is named "test"

select t1.*, t2.[s.no] as MatchSNo from test t1, test t2
where t1.id = t2.id and t1.amount = t2.amount
and t1.acccode = t2.acccode and t1.[s.no] <> t2.[s.no]
order by t1.id, t1.Amount, t1.accCode, [s.no]