CODEWITHSUNDEEP
sqlsql-server
Federico Martinez
Federico Martinez

Reputation: 65

select penultimate record with a filter

I post yesterday a question to find the way to obtain in a series of rows, the one that has been added to the table in a penultimate place (filtering by date) see the link.

Yestedar question

My problem for yesterday's question was that when I tried to apply a filter the query did not work, and what I was missing was to apply the filter in all select.

For example (query 1): 

SELECT prdid, lstfchdes, lstid, lstpusrupd FROM price
WHERE lstfchdes = (SELECT MAX(lstfchdes)
                    FROM price 
                    WHERE lstfchdes < (SELECT MAX(lstfchdes)  
                    FROM price
                    where prdid ='PRO167' and lstid='l04')
                    and prdid = 'PRO167' AND lstid='l04')
and lstid='l04' and prdid='PRO167'

This works correctly, but i have a problem when the elements that i use to filter, they have different penultimate date.

For example (query 2): 

select * from precios (nolock)
WHERE lstid='L04' and prdid='PRO167'
order by lstfchdes desc

Return this:

ResultQuery2

The penultimate date is 2018-03-03.

If i run the first query, that's return correctly:

ResultQuery1

But if i try to use another prdid that have different penultimate date, it doesn't work.

Example (query 3) 

select lstid, prdid, lstfchdes from precios (nolock)
WHERE lstid='L04' and prdid='NO7415'
order by lstfchdes desc

ResultQuery3

So, i'm trying this but i understand that maybe dont work correctly beacuse have different penultimate date (query 4): 

SELECT prdid, lstfchdes, lstid, lstpusrupd FROM precios (nolock)
WHERE lstfchdes = (SELECT MAX(lstfchdes)
                    FROM precios (nolock)
                    WHERE lstfchdes < (SELECT MAX(lstfchdes)  
                    FROM precios (nolock)
                    where prdid IN ('PRO167', 'NO7415') and lstid='l04')
                    and prdid IN ('PRO167', 'NO7415') AND lstid='l04')
and lstid='l04' and prdid IN ('PRO167', 'NO7415')

ResultQuery4

I'm trying this, but not work: 

SELECT prdid, lstfchdes, lstid, lstpusrupd FROM precios (nolock)
WHERE prdid IN (select prdid from precios where prdid='PRO167' and lstid='l04'
                and lstfchdes = (SELECT MAX(lstfchdes) from precios
                                 WHERE lstfchdes < (SELECT MAX(lstfchdes)
                                 from precios
                                 where prdid ='PRO167' and lstid='l04')
                                 and prdid ='PRO167' and lstid='l04')
                                 and prdid ='PRO167' and lstid='l04')
 and prdid ='PRO167' and lstid='l04'

But this is returning all the rows where that code exists and not just the penultimate date.

Any suggestion? Thanks!!

Upvotes: 0

Views: 178

Answers (1)

shawnt00
shawnt00

Reputation: 17915

I'm thinking something like this will help you. What you need is the max date per prdid rather than just a single max date across the entire query. Since you're not dealing with a single result, a subquery isn't going to cut it.

with
    p as (select * from precios where prdid in ('PRO167', 'NO7415') and lstid = 'L04'),
    p_rnk as (
        select *, row_number() over (partition by prdid order by lstfchdes desc) as rn
        from p
    )
select * from p_rnk where rn = 2;

The basic idea is that thee row_number() function essentially splits out the rows into groups and then counts them out in order of descending dates. If there's potential for ties then you might have some complications but this is definitely down the right path.

Upvotes: 1

Related Questions