Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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
add comment

1 Answer

up vote 1 down vote accepted

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
, TXN_DATE DATETIME
)

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:

select 
  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
  A.TXN_DATE

Returns the following results:

TXN_ID    SUBSEQUENT_TXN_ID
--------- -----------------
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.

share|improve this answer
    
Is there any way I can represent this in relational algebra? –  Imray 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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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