locked
How to give serial number for a column with same value in sql server 2005 RRS feed

  • Question

  • User-133573814 posted

    Hi friends,

    I have issue in giving serial number for a column in the select query.  Please give me the query for this condition.

    Here is the case:

    PatientID Medicationid Output
    31 53 1
    31 53 1
    31 53 1
    31 53 1
    31 53 1
    31 53 1
    31 54 2
    31 55 3
    31 55 3
    31 56 4
    31 56 4
    31 57 5
    84 137 1
    84 137 1
    84 139 2
    84 139 2
    84 140 3
    84 141 4
    Friday, August 10, 2012 3:55 AM

Answers

  • User1553110397 posted

    Hope this will help you

    declare @tmpHeader table(headerid int identity(1,1),patientid int,medicationid int)
    declare @tmp table(patientid int,medicationid int)
    
    insert into @tmpHeader(patientid,medicationid) select distinct patientid,medicationid from @tmp group by patientid,medicationid
    
    
    select d.patientid,d.medicationid, h.headerid from @tmpHeader h inner join @tmp d on
    h.patientid = d.patientid and h.medicationid = d.medicationid

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 10, 2012 4:39 AM
  • User-1378764701 posted

    Hi,

    In Oracle use this qry

    select pid,mid,dense_rank() over(partition by pid order by mid) slno
    from drop1

    Regards

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 10, 2012 9:08 AM

All replies

  • User1553110397 posted

    Hope this will help you

    declare @tmpHeader table(headerid int identity(1,1),patientid int,medicationid int)
    declare @tmp table(patientid int,medicationid int)
    
    insert into @tmpHeader(patientid,medicationid) select distinct patientid,medicationid from @tmp group by patientid,medicationid
    
    
    select d.patientid,d.medicationid, h.headerid from @tmpHeader h inner join @tmp d on
    h.patientid = d.patientid and h.medicationid = d.medicationid

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 10, 2012 4:39 AM
  • User-1378764701 posted

    Hi,

    In Oracle use this qry

    select pid,mid,dense_rank() over(partition by pid order by mid) slno
    from drop1

    Regards

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, August 10, 2012 9:08 AM