Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2012
    Posts
    11

    Unanswered: SQL Server 2000 problem

    Hello,

    Still learning. I need to put the serial numbers for results in group in SQL Server 2000. Please see below:


    My input
    procedureid procname
    1 A
    1 B
    2 A
    2 B
    2 C
    2 D
    3 A
    3 B
    3 C

    Output I need:

    procedureid procname serial_num
    1 A 1
    1 B 2
    2 A 1
    2 B 2
    2 C 3
    2 D 4
    3 A 1
    3 B 2
    3 C 3

    Here is my table:


    create table po(
    procedureid int,
    procname varchar(10),
    )

    insert into po values (1,'A')
    insert into po values (1,'B')
    insert into po values (2,'A')
    insert into po values (2,'B')
    insert into po values (2,'C')
    insert into po values (2,'D')
    insert into po values (3,'A')
    insert into po values (3,'B')
    insert into po values (3,'C')

    Thank you very much for looking into this.

    bd532

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    SELECT procedureid, procname,
    RANK() OVER(PARTITION BY procedureid ORDER BY procname ASC) AS serial_num
    FROM po
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Window functions (including Rank()) are not available until SQL Server 2005 versions and later.

    Any particular reason you are using a 12 year old technology instead of one of the newer options?
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Incidentally it is doable, but very inefficient.

    I haven't tested this, so consider this the idea to work from.
    Code:
    SELECT x.procedure_id
         , x.proc_name
         (
           SELECT Count(*)
           FROM   po As ranking
           WHERE  ranking.procedure_id = x.procedure_id
           AND    ranking.proc_name <= x.proc_name
         ) As ranking
    FROM   po As x
    George
    Home | Blog

  5. #5
    Join Date
    Mar 2012
    Posts
    11

    cheap

    Our boss is too cheap to get the new one

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    by George!!! I think he's got it...except there are a few typo's and a missing commas

    Code:
    SELECT x.procedureid
         , x.procname
         , (
           SELECT Count(*)
           FROM   po As ranking
           WHERE  ranking.procedureid = x.procedureid
           AND    ranking.procname <= x.procname
         ) As ranking
    FROM   po As x
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Mar 2012
    Posts
    11
    Works!! Thanks George and Brett.

  8. #8
    Join Date
    Mar 2012
    Posts
    11

    little modification on output

    SQL server 2000


    Output I need:

    procedureid procnames
    1 A,B
    2 A,B,C,D
    3 A,B,C

    I tried using user defined function as suggested in some other forums but I guess functions don't exist for 2000.

  9. #9
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by bd532 View Post
    SQL server 2000


    Output I need:

    procedureid procnames
    1 A,B
    2 A,B,C,D
    3 A,B,C

    I tried using user defined function as suggested in some other forums but I guess functions don't exist for 2000.
    User Defined Functions (UDF) are functions designed by users as the name suggests.

  10. #10
    Join Date
    Mar 2012
    Posts
    11

    This function works

    CREATE FUNCTION dbo.udf_GetProcNames(@ProcedureID int)
    RETURNS VARCHAR(1000) AS

    BEGIN
    DECLARE @ProcNameList varchar(1000)

    SELECT @ProcNameList = COALESCE(@ProcNameList + ', ', '') + s.procname
    FROM po s
    WHERE s.procedureid = @ProcedureID

    Return @ProcNameList
    END


    select procedureid, dbo.udf_GetProcNames(procedureid) as procnames
    from po
    group by procedureid

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    All of these sound suspiciously like display layer problems. How is the data being displayed to the end user?

  12. #12
    Join Date
    Aug 2004
    Location
    Dallas, Texas
    Posts
    831
    Quote Originally Posted by bd532 View Post
    CREATE FUNCTION dbo.udf_GetProcNames(@ProcedureID int)
    RETURNS VARCHAR(1000) AS

    BEGIN
    DECLARE @ProcNameList varchar(1000)

    SELECT @ProcNameList = COALESCE(@ProcNameList + ', ', '') + s.procname
    FROM po s
    WHERE s.procedureid = @ProcedureID

    Return @ProcNameList
    END


    select procedureid, dbo.udf_GetProcNames(procedureid) as procnames
    from po
    group by procedureid
    Good deal! Now that's a UDF.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •