Selecting the Last Row In a Table Where Yada Yada Yada

• Robert Wetherill

  Right there with Babe

  Points: 790

  More actions

  May 3, 2006 at 9:00 am

  #169254

  I have one table.

  table-a

  field-item

  field-cost

  field-effective-date

  Record sample

  Item    Cost   Effective Date

  abc     1.00    04/04/2005

  abc    1.10     09/04/2005

  abc    1.20     12/04/2005

  abc     1.30    12/31/2005

  def      2.38    12/31/2005

  In a SELECT statement, If I am sorting by field-item by field-effective-date, how can I find the last record where field-item = 'abc'

  I can't find a "Last" command or a "LastRow" command or "Bottom" command.

  Thanks

   

   

• Pam Brisjar

  SSChampion

  Points: 12094

  More actions

  May 3, 2006 at 9:08 am

  #635672

  try this:

  set rowcount 1

  select field-item, cost, field-effective-date

  from MyTable

  where field-item = '123'

  ORDER BY field-effective-date DESC

  --or--

  select TOP 1 field-item, cost, field-effective-date

  from MyTable

  where field-item = '123'

  ORDER BY field-effective-date DESC