get current and previous record

[wudang26]

I have this query that gets a name based on the a begin date in my table <= sysdate and then the end date.

I get one value from the name field when I do this. I would like to get the previous record
So there would be 2 values in the results. Any help would be appreciated.

name2 - previous record
name1


select name
FROM ps_name_tbl
WHERE (begin_dt <= sysdate) and (end_dt >= sysdate) and (career = 'Program')
GROUP BY name, career, begin_dt, end date
ORDER BY nam

 

[Olaf Doschke]

You will then need to do a 2 pass query, kind of. Because the memory of the sql engine is not a human memory, you always only adress the current record while the table is scanned (if the fetched records are not optimised by an index, which only will pick the result records amyway).

So in short: You neither have access to the next or previous record.

But let's see what we have at hand:
1. A condition for the last record you want
2. A count (2) of the records you want - If the condition is changed, this could be turned into a TOP 2 query to get 2 records.
3. an order: nam - If this is expanded for the TOP2 query you can talk of a previous record in date order. I just don't know if that should be in end_dt or start_dt order

Get the idea?

Instead of aiming for the last record with WHERE (begin_dt <= sysdate) and (end_dt >= sysdate), transform this condition to filter all the records up to this last record, including the "previous" record. Then you can fetch that previous record by TOP 2.

As you group by begin_dt and then end_dt what would be valid for the previous record?
1. It's end_dt is earlier or equal to the record you fetch now
2. It's begin_dt is earlier or equal to the record you fetch now

Both dates couldn't be exactly the same, otherwise you'd already have it in your result.

Let me make an assumption here, to get on with the solution: As you query for a record having begin_dt before sysdate and end_dt after sysdate, that means sysdate is between these dates, previous records could have sysdate before begin_dt, so to get all records up to the last one you could drop the condition about begin_dt and in the first place just filter for all records with end_dt>=sysdate and then just take the BOTTOM 2 of these.

Even if that would not fit your needs exactly you can change that later, as I said, that's an assumption.

To get BOTTOM 2 via the TOP clause you just need to reverse order, so finally I come to this query:

select TOP 2 name
FROM ps_name_tbl
WHERE (end_dt >= sysdate) and (career = 'Program')
GROUP BY name, career, begin_dt, end_dt
ORDER BY nam DESC, begin_dt DESC, end_dt DESC

This will give you 2 result records in inverse order, so instead of

name2
name1

you would get
name1
name2

But that won't hurt, would it?

Bye, Olaf.

 

[markros]

In this case you most likely need

select top (2) name
FROM ps_name_tbl
WHERE (begin_dt <= sysdate) and (end_dt >= sysdate) and (career = 'Program')
GROUP BY name, career, begin_dt, end date
ORDER BY name

PluralSight Learning Library

 

[Olaf Doschke]

markros,

If the original query gives exactly one record (as I understand it), then keeping that Whereclause and adding Top will not add a second record. That's the reason I also changed the where clause, making some assumptions.

I'm sure, wudang, you can take the idea of top 2 and adapt it the way you need. You know better what top 2 or bottom 2 records you need. If your where clause filters for the last record in date order, I think you need to loosen it, to get a previous record in your result at all, and to make use of top 2.

Bye, Olaf.