Results 1 to 13 of 13
  1. #1
    Join Date
    Apr 2004
    Posts
    49

    Unanswered: Select query with incremental column on the fly?

    Hi, all.
    I want to Select query with incremental column on the fly.

    For example
    Use pubs
    GO
    select * from jobs where job_desc like '%e%' Order by max_lvl
    returns
    job_id job_desc min_lvl max_lvl
    1 New Hire - Job not specified 10 10
    12 Editor 25 100
    13 Sales Representative 25 100
    ...

    I want to add here Rank Column numbering in order
    select RankOnTheFly, * from jobs where job_desc like '%e%' Order by max_lvl
    Then result will be..
    Rank job_id job_desc min_lvl max_lvl
    1 1 New Hire - Job not specified 10 10
    2 12 Editor 25 100
    3 13 Sales Representative 25 100
    ..

    I can get the result using cursor and looping throught and inserting or Using Identity function.
    But, I saw before there is just one simple Select query doing that.

    Does anyone know this?
    Thank you..
    MCSD .NET, SCJP, SCJWD

  2. #2
    Join Date
    Feb 2007
    Posts
    62
    That's front end work.
    You can use row_number() if you are on SQL Server 2005

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Or, in SQL Server 2000 you can create a temporary table with a defined identity column, insert your data into it, and then select from that for you output.
    If it's not practically useful, then it's practically useless.

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

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    good work guys. rownumber is the best approach in 2k5 and the temp table created and then inserted into is the best 2K approach. however the temp table approach with the IDENTITY function with the SELECT INTO is slower.
    “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
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Or subselect with a count(*) and a comparison to the jobid.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by pootle flump
    Or subselect with a count(*) and a comparison to the jobid.
    ....which, though it has the advantage of being able to run as a view in a single SELECT statements with subqueries, has the disadvantage of being by far the slowest solution.
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    theta joins, gotta love 'em

    i tend to agree with the opinion that numbering result rows is a front end issue
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Apr 2004
    Posts
    49

    There is answer..

    Hi all,
    Thank you all for replies.
    I did it with temp table. And I'm using sql2000.
    Because I saw the solution from
    http://www.sql-server-performance.com
    about 3 years ago. It was simple select statement doing the job without using Temp table with sql2000. I searched for a while and couldn't find it.
    So, I hope anybody know the solution.
    It speeds up and make clear.
    MCSD .NET, SCJP, SCJWD

  9. #9
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Quote Originally Posted by pootle flump
    Or subselect with a count(*) and a comparison to the jobid.
    sorry poots. the blind dude is right. yours is much slower. I have been concentrating my reading on performance lately and this problem was in one of my books recently. that rownumber function in 2K5 is absolutly the fastest though with very little curve upwards as the amount of data increases.

    performance tuning seems to be what makes people the happiest. "Oooh Ahhh see how much faster it is"
    “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.

  10. #10
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by Thrasymachus
    performance tuning seems to be what makes people the happiest. "Oooh Ahhh see how much faster it is"
    That's what she said ...

    -- This is all just a Figment of my Imagination --

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    tom, that's amazing!! i thought i was the only guy in the world who said that

    i've been saying it since high school, in the '60s

    it's hilarious how often you can say it, eh?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I never said it was fast - just another option

    I am surprised there is little overhead for OVER. Did you test it using columns in the order by clause that are not simply a repeat of the clustered index? I would have thought that logical ordering has got to have a cost relative to the amount of ordering the engine needs to do.

    Ah actually I know you read Ben Gen - are you referring to this?
    http://www.insidetsql.com/OVER_Clause_and_Ordered_Calculations.doc
    I've read the first half - got bored after that
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    wow, pootle, great link *bookmarked*

    The fact that the same ORDER BY clause serves two different purposes is problematic. You might want to choose rows based on one sort criteria, and sort the output rows based on another (or not at all). Also, many programmers don’t realize that when you use a TOP query to define a table expression (view, inline table function, derived table, CTE), the ORDER BY clause loses its presentation meaning. For example, in the following query:
    Code:
    SELECT *
    FROM (SELECT TOP(3) OrderDate, OrderID, CustomerID, EmployeeID
          FROM dbo.Orders
          ORDER BY OrderDate DESC, OrderID DESC) AS D;
    Here there’s no guarantee that the output will be returned in a particular order (even though in terms of optimization it is likely that it will). This misunderstanding leads to absurd uses of the TOP option, e.g., “sorted views”:
    Code:
    CREATE VIEW dbo.SortedOrders
    AS
    SELECT TOP(100) PERCENT OrderDate, OrderID, CustomerID, EmployeeID
    FROM dbo.Orders
    ORDER BY OrderDate DESC, OrderID DESC;
    Many programmers don’t realize that the following query is not guaranteed to return the rows sorted:
    Code:
    SELECT * FROM dbo.SortedOrders;
    And that this use is absurd.
    emphasis added

    one of the few things i did know before reading that paper

    (i skimmed parts, though)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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