Selecting Last and Second to Last Values

[markcobb]

Please help,
I have a table called Incident, these Incidens then have an Audit Table to show when the last call etc was made. These are denoted by a Worknoteid, what I want to do is create a view that only returns the second to last and the last value from this table. E.G

Incident 1234 has 5 entries on the Audit

1
2
3
4
5

I want to bring back 4 and 5

 

[AWithers]

it will be somehting like

select top 2 from yourtable order by yourauditidfield desc

HTH

Andy

 

[markcobb]

Sorry,
I forgot to add that this is not for one record but for a multitude of records, therefore the top 1 etc would be fine against one Incident but not against many. Any other ideas

 

[r937]

you can use a correlated subquery for this

select I.id, A.worknoteid
  from incident I
     , audit    A
 where I.id = A.incidentid
   and A.worknoteid in 
       ( select top 2
                worknoteid
           from audit
          where incidentid = A.incidentid 
       order by worknoteid desc )

rudy

http://rudy.ca/