SQL Help selecting last action of a given event

• Linus Tor

  Newbie

  Points: 2

  More actions

  March 23, 2005 at 10:50 pm

  #76264

  Hi all,

  I'm stuck on a problem which I'd appreciate any help. Given the following table, I want to select the row before the row where Class = '$'. So the result should give me ID 1, 4, and 6. I would like to avoid using Cursors to solve this problem as the number of rows returned will be very large.

  ID             Class

  1              T

  2              $

  3              F

  4              C

  5              $

  6              F

  7              $

  8              $

   

  Thanks,

  Linus

• wz700

  SSCertifiable

  Points: 7133

  More actions

  March 23, 2005 at 11:09 pm

  #548652

  Assume id is alwayse increase and has index on it.

  scalar function use max function can help.

  Like

  create function fn_rowbefore$ (@id int)

  returns int

  as

  begin

    declare @ren int

    select @ren = max(id) from tablename where id < @id and class != '$'

    return @ren

  end

  select distinct dbo.fn_rowbefore$(id) from tablename where class = '$'

• Linus Tor

  Newbie

  Points: 2

  More actions

  March 24, 2005 at 5:29 am

  #548701

  Thanks wz700. That certainly resolve my little problem.

  Cheers,

  Linus

• Jeff Moden

  SSC Guru

  Points: 1004638

  More actions

  March 24, 2005 at 6:38 am

  #548720

  As WZ700 said, I'm assuming that ID is a "no skip" sequential IDENTITY column...

  This will "find" everything that meets your criteria all at once in a single run...

  SELECT *

    FROM yourtable

   WHERE ID IN (--Deived table finds the correct ID's

                SELECT ID-1 AS ID

                  FROM yourtable

                 WHERE Class = '$')

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)