HOW TO SELECT PREVIOUS ROW VALUE? [duplicate]

by

This one should work fine for both databases:

SELECT cardevent.ID, cardevent.Value, 
  (SELECT TOP 1 F1.Value
   FROM cardevent as F1
   WHERE F1.ID < cardevent.ID
   ORDER BY F1.ID DESC
   ) AS Prev_Value
FROM cardevent

Update: Assuming that ID is not unique, but that the combination of ID and Value is unique (you must specify what imposes your ordering on the table, so one knows what is the previous row), you must use this query:

select cardevent.ID, cardevent.Value, 
    (select TOP 1 F1.Value from cardevent as F1
     where (F1.ID < cardevent.ID) or (F1.ID = cardevent.ID and F1.Value < cardevent.Value)
     order by F1.ID DESC, F1.Value DESC
    ) AS Prev_Value
from cardevent

More Related Contents:

  1. SQL Access ” Count “? [closed]
  2. How do I use parameters in VBA in the different contexts in Microsoft Access?
  3. SQL multiple join statement
  4. How to get matching data from another SQL table for two different columns: Inner Join and/or Union?
  5. Case expressions in Access
  6. How do I count unique items in field in Access query?
  7. Date Difference between consecutive rows
  8. Pivoting data in MS Access
  9. coalesce alternative in Access SQL
  10. Using Excel VBA to export data to MS Access table
  11. is there a group_concat function in ms-access?
  12. Why does a LIKE query in Access not return any records?
  13. How to SUM two fields within an SQL query
  14. [] brackets in sql statements
  15. How to use system username directly in MS Access query?
  16. Row numbers in query result using Microsoft Access
  17. can we list all tables in msaccess database using sql?
  18. Use Access SQL to do a grouped ranking
  19. Access – compare two tables and update or insert data in first table
  20. “You tried to execute a query that does not include the specified aggregate function”
  21. How to delete in MS Access when using JOIN’s?
  22. SQL: Using NULL values vs. default values
  23. Editing Record issues in Access / SQL (Write Conflict)
  24. How do I create a table alias in MySQL
  25. Select query with date condition
  26. Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess
  27. Microsoft Access – Case Query
  28. SQL SET DEFAULT not working in MS Access
  29. CONCAT equivalent in MS Access
  30. Check if access table exists

Leave a Comment