SlNo in Select Query

• Prakash-205770

  Ten Centuries

  Points: 1079

  More actions

  April 11, 2006 at 6:22 am

  #112634

  I have the following Table : Emp

  EmpID	Dept	EmpName
  1	prdn	ravi
  2	prdn	rani
  3	prdn	raja
  4	IT	pathy
  5	IT	mahi
  6	IT	Latha
  7	Admin	Neela
  8	Admin	Raju
  9	Admin	Ramu

   

  I want a select query to form the following based on Dept.

  Sno	ID	Dept	EmpName
  1	1	prdn	ravi
  2	2	prdn	rani
  3	3	prdn	raja
  1	4	IT	pathy
  2	5	IT	mahi
  3	6	IT	Latha
  1	7	Admin	Neela
  2	8	Admin	Raju
  3	9	Admin	Ramu

   

  Thanks,

  R.Prakash

  ---

  Kindest Regards,

  R

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  April 11, 2006 at 6:48 am

  #631896

  select EmpID - MinEmp + 1 as Sno, EmpID, e.Dept, EmpName

  from Emp e left join (select Dept, min(EmpID) as MinEmp from Emp group by Dept) g

  on e.Dept = g.Dept

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  April 11, 2006 at 6:57 am

  #631898

  or, if EmpID not consecutive

  SELECT (SELECT COUNT(*) FROM [Emp] b WHERE b.Dept = a.Dept AND b.EmpID <= a.EmpID)

  ,a.EmpID, a.Dept, a.EmpName

  FROM [Emp] a

  ORDER BY a.Dept ASC, a.EmpID ASC

  Far away is close at hand in the images of elsewhere.
  Anon.