SQL: using a field as another field using where conditions

Question

I have a table in a database:

DRN | VoucherNo | CrDRN
-----------------------    
1   |80        |?
2   |11        |?
3   |11        |?
4   |80        |?
5   |11        |?

I'm trying to get the DRN number for the CrDRN column.

It is calculated like this:

The CrDRN of every row with the voucher type will be its DRN number
Any row with the VoucherType of 11 will have the DRN number of the previous record where the VoucherType equals 80

So in this instance the CrDRN column values will be:

I was thinking I'd need to use a Where VoucherType = 80

But I can't wrap my head around the concept of the second 80

Any help would be appreciated

This sounds dangerous - I would strongly advise you redesign this table so no row's values are dependent on other rows. — Conduit
– Conduit, Commented Jul 9, 2014 at 20:33

Gordon Linoff · Accepted Answer · 2014-07-09 20:34:35Z

1

You can implement this logic using a correlated subquery, assuming that "previous" is based on the drn column:

select drn, voucherno,
       (case when voucherno <> 0 then t.drn
             else (select t2.drn
                   from table t2
                   where t2.id < t.id and
                         t2.voucherno = 80
                   order by t2.drn desc
                   limit 1
                  )
        end) as crDRN
from table t;

answered Jul 9, 2014 at 20:34

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user927735 Over a year ago

All of this is stored in one table. And yes, essentially this is to see which of the records that have 11 belong to which records above them that have a vouchertype of 80. Basically like a deposit slip had a number of cheques that belong to it, this is how the system would know that these cheques belong to that deposit slip

Collectives™ on Stack Overflow

SQL: using a field as another field using where conditions

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related