Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2005
    Posts
    4

    Unanswered: RowNumber using SQL Query

    Hi All,
    I have following table structure,
    HTML Code:
    ----------------------------------------------------------------------
    ChallanID      ProductID        PublicationDate    Description    Qty     Amt
    ----------------------------------------------------------------------
    43             9                4/1/2006           ABC          1         880
    43             10               5/1/2006           BCA          1         930
    43             11               5/1/2006           CBA          1         230
    I want a sql query which select all the record with a serial number eg:

    HTML Code:
    ---------------------------------------------------------------------
    SN#    ChallanID    ProductID    PublicationDate   Description    Qty     Amt
    ----------------------------------------------------------------------
    1     43             9           4/1/2006           ABC          1         880
    2     43             10         5/1/2006           BCA          1         930
    3     43             11         5/1/2006           CBA          1         230

  2. #2
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    use a temp table with identity column and insert your result into the temp table and select it back.
    -----------------
    KH


  3. #3
    Join Date
    Jun 2003
    Posts
    269
    U havent mentioned about how records to be ordered? In what order u want to generate serial No:?
    I love cursor,though I never use it.Cos' I dont want to misuse it.
    ----------------------------------------------

    ----cheers
    ----http://mallier.blogspot.com

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    temp table shemp table...

    SELECT count(*) as [SS#],a.LastName
    FROM Employees a join
    Employees b
    on a.LastName >= b.LastName
    group by a.LastName
    order by a.LastName

    ps. I got this example from somewhere and it is not original work. If the original author sees this and takes any offense I am will to erase from the forum.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Quote Originally Posted by Thrasymachus
    SELECT count(*) as [SS#],a.LastName
    FROM Employees a join
    Employees b
    on a.LastName >= b.LastName
    group by a.LastName
    order by a.LastName
    No, that only works if you use a unique key i.e.
    Code:
    select id=1,name='ccc' into #t1 union all
    select 3,'bbb' union all
    select 4,'aaa' union all
    select 9,'bbb' 
    
    select count(*) as [ss#], name=min(a.name)
    from #t1 a, #t1 b
    where a.id>=b.id
    group by a.id
    order by 1
    else use a temp table with identity column as suggested by khtan
    Code:
    select ss#=identity(int,1,1),name into #t2 from #t1 order by name
    select * from #t2
    Last edited by pdreyer; 05-17-06 at 09:46.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Problem is, either way you have no guarantee that the "serial number" for any record won't change as the contents of the table changes. Seems to me a "serial number" is expected to be static, so you really should add it as a permanent column to your table (perhaps as an identity datatype).
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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