Previous record of same recordset

[Mukhmoor]

I have a table order n a table payment. I need to go to the last record in payment table n perform a check on datepaid. I achieved this by using query with last(paymentid). I want to know tht if datepaid of last paymentid is null. Query should fetch the data of previous paymentid for a specific order where the datepaid is not null. How this can be done? Can someone help me?

 

[jdraw]

Try this site for info

http://allenbrowne.com/subquery-01.html

Look at the topic Get the value in another record

 

[Mukhmoor]

Thnx jdraw but i m not getting required result with this :-(

 

[lutajuca]

pseudo code of your query (one,two… ?) would be useful. However, now :
this construction works:
a) …., iif(isnull(select 1…),(select 2 …),(select 3…)) as ... in select for a single information
b) where id= iif(isnull(select 1…),(select 2 …),(select 3…)) or in a condition

try ….

 

[Galaxiom]

Never trust the First and Last functions. Always use Min and Max.

You can achieve you goal using a self join on the table.
Try searching the forum for "self join". I have contributed a number of soutions to this kind of problem using self joins.

 

[Thales750]

Never trust the First and Last functions. Always use Min and Max.

You can achieve you goal using a self join on the table.
Try searching the forum for "self join". I have contributed a number of soutions to this kind of problem using self joins.

Aggreed but to add to that, I now time stamp every record, in every table, everywhere. In multi-location replicated systems with composite foriegn keys, the only way to find out the exact last record is to use time stamps. And of course the min-max function.