I need to come up with a query that can do the following:

I have a table with a bunch of transaction records, whose attributes are not important, other than the attribute Date.

I need to come up with a query that will return a relation (table) with two columns: the first, will contain the transaction IDs of all transactions, the second, will contain the transaction ID of the transaction that followed it immediately.

I can't think of any way to do this outside of programming it in Java. Does anyone have any advice?

  • It would help if you provided the CREATE TABLE Transactions; statement. What is the datatype of the column Date? Is it unique? And what DBMS do you use? – ypercubeᵀᴹ Nov 4 '13 at 9:58

You can use a correlated subquery to solve this problem.

Consider the following SQL Fiddle (which includes a lot of assumptions about your table and data): Working SQL Fiddle

With this table definition and sample data:

create table TXN
( ID int

insert into TXN (ID, TXN_DATE) values (1, '2013-01-01');
insert into TXN (ID, TXN_DATE) values (2, '2013-02-02');
insert into TXN (ID, TXN_DATE) values (3, '2013-03-03');
insert into TXN (ID, TXN_DATE) values (4, '2013-04-04');
insert into TXN (ID, TXN_DATE) values (5, '2013-05-05');

This query:

  A.ID as TXN_ID
, (select top 1 B.ID from TXN B
   where B.ID > A.ID) as SUBSEQUENT_TXN_ID
from TXN A
order by

Returns the following results:

--------- -----------------
1         2 
2         3 
3         4 
4         5 
5         (null) 

The SQL above assumes your dates are unique. If they aren't then you would have to do something else to distinguish the records. For example, you might have to make your correlation more complex: where B.ID > A.ID might have to become: where B.ID >= A.ID and B.ID <> A.ID (or B.ID > A.ID, if your IDs are sequential or at least increasing).

If dates aren't unique and you don't have a sequential identifier to enforce the proper sequence, you may not get perfect, repeatable results, although this would also be true of a procedural approach because you need to be able to sort your records in your select to be able to sequence them.

  • Is there any way I can represent this in relational algebra? – CodyBugstein Nov 4 '13 at 13:52
  • @Imray - I'd suggest googling around for "correlated subquery relational algebra" - Someone's probably figured it out. – Joel Brown Nov 4 '13 at 17:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.