CODEWITHSUNDEEP
sqlt-sql
jwe330
jwe330

Reputation: 1

max records with dense rank

Is there a better alternative to using max to get the max records. I have been playing with dense rank and partition over with the below query but I am getting undesired results and poor performance.

select Tdate = (Select  max(Date)  
                    from Industries 
                    where Industries.id = i.id 
                        and Industries.Date <= '22 June 2011')
    from #ii_t i

Many Thanks.

Upvotes: 0

Views: 2954

Answers (2)

billinkc
billinkc

Reputation: 61239

The supplied query doesn't use the DENSE_RANK windowing function. Not being familiar with your data structure, I believe your query is attempting to find the largest value of Date for each Industry id, yes? Rewriting the above query to use a ranking function, I would write it as a common table expression.

;
WITH RANKED AS
(
    SELECT
        II.*
    -- RANK would serve just as well in this scenario
    ,   DENSE_RANK() OVER (PARTITION BY II.id ORDER BY II.Date desc) AS most_recent
    FROM Industries II
    WHERE
        II.Date <= '22 June 2011'
)
, MOST_RECENT AS
(
    -- This query restricts it to the most recent row by id
    SELECT
        R.*
    FROM
        RANKED R
    WHERE 
        R.most_recent = 1
)
SELECT
    *
FROM
    MOST_RECENT MR
    INNER JOIN
        #ii_t i
        ON i.id = MR.id

Also, to address the question of performance, you might need to look at how Industries is structured. There may not be an index on that table and if there is, it might not cover the Date (descending) and id field. To improve the efficiency of the above query, don't pull back everything in the RANKED section. I did that as I was not sure what fields you would need but obviously the less you have to pull back, the more efficient the engine can be in retrieving data.

Upvotes: 1

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

Try this (untested) code and see if it does what you want. By the looks of it, it should return the same things and hopefully a bit faster.

select Tdate = max(Industries.Date)    
from #ii_t i
  left outer join Industries
    on Industries.id = i.id and
       Industries.Date <= '22 June 2011'
group by i.id

Upvotes: 0

Related Questions