Help retrieving the last X rows of a DB based on the result of a select

• christopher_craig

  Grasshopper

  Points: 21

  More actions

  December 9, 2008 at 1:25 pm

  #127417

  First let me thank any and all who can help me. I'm new to SQL Querying and reporting and have figured out a few things for myself, but this one's stumped me.

  What I need to do is return the previous (X) number of rows from the DB based on the results of my specific SELECT.

  DB Snippet:

  14:57:59 DEBUG MACEPickListManager - GetValueInfo cache hit - DispatchArea 095331

  14:57:59 DEBUG MACEPickListManager - GetValueInfo cache hit - OrderPriority A2

  14:58:42 DEBUG MACEPickListManager - GetValueInfo cache hit - JobCode T33

  14:58:42 DEBUG MACEPickListManager - GetValueInfo cache hit - BusinessUnit 09533

  14:58:43 DEBUG MACEPickListManager - GetValueInfo cache hit - DispatchArea 095331

  14:58:43 DEBUG MACEPickListManager - GetValueInfo cache hit - OrderPriority A2

  15:38:38 INFO MainForm - -------- MA-CE Startup --------

  15:38:38 INFO MainForm - OS is Microsoft Windows CE 5.1.478 / CLR is 2.0.7045.0

  SELECT Statement:

  SELECT [pkid]

  ,[log_content]

  FROM [Handheld_Logs].[dbo].[sample_log]

  where [log_content] LIKE '%MA-CE Startup%'

  This will return:

  15:38:38 INFO MainForm - -------- MA-CE Startup --------

  my dilema is, based on this query, how do I also retrieve the above AND the previous 5 rows of the DB?

  NOTE: each row has a unique incremental identification value in the "pkid" column.

  any help would be appreciated. Thanks.

• Steve Jones - SSC Editor

  SSC Guru

  Points: 741485

  More actions

  December 9, 2008 at 2:41 pm

  #909963

  You want the 5 rows prior to this line?

  SELECT top 5 [pkid]

  ,[log_content]

  FROM [Handheld_Logs].[dbo].[sample_log]

  where [pkid] + 5 > ( SELECT [pkid]

  ,[log_content]

  FROM [Handheld_Logs].[dbo].[sample_log]

  where [log_content] LIKE '%MA-CE Startup%'

  )

  order by pkid