Jeff's SQL Server Blog

Random Thoughts & Cartesian Products with Microsoft SQL Server
posts - 157, comments - 2686, trackbacks - 64

My Links

Advertisement

News

Welcome to my weblog. My name is Jeff Smith, I am software developer in Boston, MA and I was recently named a 2009 SQL Server MVP. Check in frequently for tips, tricks, commentary and ideas on SQL Server and .NET programming.


Subscribe





Archives

Post Categories

Programming

Sports

SQL

SQL Server 2005: Using PARTITION and RANK in your criteria

The RANK and PARTITION features in 2005 are simply amazing.  They make so many "classic" SQL problems very easy to solve.  For example, consider the following table:

create table Batting
 (Player varchar(10), Year int, Team varchar(10), HomeRuns int, primary key(Player,Year))

insert into Batting
select 'A',2001,'Red Sox',13 union all
select 'A',2002,'Red Sox',23 union all
select 'A',2003,'Red Sox',19 union all
select 'A',2004,'Red Sox',14 union all
select 'A',2005,'Red Sox',11 union all
select 'B',2001,'Yankees',42 union all
select 'B',2002,'Yankees',39 union all
select 'B',2003,'Yankees',42 union all
select 'B',2004,'Yankees',29 union all
select 'C',2002,'Yankees',2 union all
select 'C',2003,'Yankees',3 union all
select 'C',2004,'Red Sox',6 union all
select 'C',2005,'Red Sox',9


Suppose we would like to find out which year each player hit their most home runs, and which team they played for.  As a tie-breaker, return the latest year.  

In SQL 2000, we had to do it in two steps:  First, get the MAX(HomeRuns) per player, and then join back to the Batting table to return the rest of the data:

select b.*
from
batting b
inner join
(    select player, Max(HomeRuns) as MaxHR
    from Batting
    group by player
) m
on b.Player = m.player and b.HomeRuns = m.MaxHR

Player     Year        Team       HomeRuns   
---------- ----------- ---------- -----------
A          2002        Red Sox    23
B          2001        Yankees    42
B          2003        Yankees    42
C          2005        Red Sox    9

(4 row(s) affected)


Note that for player 'B', we get two rows back since he has two years that tie for the most home runs (2001 and 2003).  How do we return just the latest year?  Guess what -- it requires *another* pass through the table:

select b.*
from
batting b
inner join
(    select player, Max(HomeRuns) as MaxHR
    from Batting
    group by player
) m
    on b.Player = m.player and b.HomeRuns = m.MaxHR
inner join
(  select player, homeRuns, Max(Year) as MaxYear
   from Batting
   group by Player, HomeRuns
) y
   on m.player= y.player and m.maxHR = y.HomeRuns and b.Year = y.MaxYear

Player     Year        Team       HomeRuns   
---------- ----------- ---------- -----------
C          2005        Red Sox    9
A          2002        Red Sox    23
B          2003        Yankees    42

(3 row(s) affected)

An alternate way to do this is to calculate the "ranking" of each home run for each player, using a correlated subquery:

select b.*,
  (select count(*) from batting b2 where b.player = b2.player and b2.HomeRuns >= b.HomeRuns) as Rank
from batting b

Player     Year        Team       HomeRuns    Rank        
---------- ----------- ---------- ----------- -----------
A          2001        Red Sox    13          4
A          2002        Red Sox    23          1
A          2003        Red Sox    19          2
A          2004        Red Sox    14          3
A          2005        Red Sox    11          5
B          2001        Yankees    42          2
B          2002        Yankees    39          3
B          2003        Yankees    42          2
B          2004        Yankees    29          4
C          2002        Yankees    2           4
C          2003        Yankees    3           3
C          2004        Red Sox    6           2
C          2005        Red Sox    9           1

However, notice that we still have not handled ties! (notice that Player "B" has no #1 ranking, just two #2 rankings!) To do that, we must make things a little more complicated:

select b.*,
  (select count(*) from batting b2 where b.player = b2.player and (b2.HomeRuns > b.HomeRuns or (b2.HomeRuns = b.HomeRuns and b2.Year >= b.Year))) as Rank
from batting b

Player     Year        Team       HomeRuns    Rank        
---------- ----------- ---------- ----------- -----------
A          2001        Red Sox    13          4
A          2002        Red Sox    23          1
A          2003        Red Sox    19          2
A          2004        Red Sox    14          3
A          2005        Red Sox    11          5
B          2001        Yankees    42          2
B          2002        Yankees    39          3
B          2003        Yankees    42          1
B          2004        Yankees    29          4
C          2002        Yankees    2           4
C          2003        Yankees    3           3
C          2004        Red Sox    6           2
C          2005        Red Sox    9           1

(13 row(s) affected)

And, with that, we can use our "ranking" formula to return only the #1 rankings to get our results by moving the subquery to the WHERE clause:

select b.*
from batting b
where (select count(*) from batting b2 where b.player = b2.player and (b2.HomeRuns > b.HomeRuns or (b2.HomeRuns = b.HomeRuns and b2.Year >= b.Year))) =1

Player     Year        Team       HomeRuns   
---------- ----------- ---------- -----------
C          2005        Red Sox    9
A          2002        Red Sox    23
B          2003        Yankees    42

(3 row(s) affected)

And that's all you need to do this in SQL 2000 ! Easy, right?   Hmmm ... well, maybe not!

The new ranking functions in SQL Server 2005 let us quickly calculate each row's ranking within a set based on a partition and an ordering. Think of the partition as almost like a GROUP BY, where you the use of the word "per" in your specifications often indicate which columns you are grouping or partitioning on.  Since we want to return the top Home Run year per Player, we partition on Player.  This means that the rows for each Player will get a value of 1-x for their ranking.  The ranking itself is based on HomeRuns, so we want to return the rank for each partition ORDER'ed BY HomeRuns from high-to-low (DESC).  

Using the new features, in SQL 2005 we can use the following SELECT to return each year's Home Run ranking for each Player:

select Player, Year, HomeRuns, Rank() over (Partition BY Player order by HomeRuns DESC) as Rank
from
Batting

Now, like before, we have to deal with ties.  But now, it is much easier -- we just add a secondary sort.  Since we want to the latest year to rank higher, we just add "Year DESC" to our ORDER BY:

select Player, Year, HomeRuns,Rank() over (Partition BY Player order by HomeRuns DESC, Year DESC) as Rank
from
Batting

Much easier than the old way!  However, we are still not done.  The above SQL returns all years with the Ranking beside it.  We are only interested in returning all rows with a Rank of 1.  However, if we try:

select Player, Year, HomeRuns
from
Batting
where
 Rank() over (Partition BY Player order by HomeRuns DESC, Year DESC) = 1

We get an error:

Windowed functions can only appear in the SELECT or ORDER BY clauses.

We can easily fix this by wrapping the SELECT in a derived table:

select * from
(
select Player, Year, HomeRuns,  Rank() over (Partition BY Player order by HomeRuns DESC, Year DESC) as Rank
from
Batting
) tmp
where Rank = 1

And there you go ... It's easy to read and edit, and only 1 reference to the table is needed!  Preliminary testing also indicates that this is much more efficient than doing things "SQL 2000-style".

Other challenging SQL statements to write in the pre-2005 world are also easily solved in SQL 2005.  For example, the classic SQL problem of returning the "top x per group" based on a sort, or returning a "row number" or sequence for an entire result set or groups within that set, are now very, very easy using the RANK() feature.  Try it out!

Print | posted on Wednesday, March 28, 2007 2:18 PM | Filed Under [ T-SQL Sports SQL Server 2005 ]

Feedback

Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

Just for completeness, you can also do it without the RANK/PARTITION via:

SELECT a.Player, a.Year, a.Team, a.HomeRuns
FROM Batting AS a
LEFT OUTER JOIN Batting AS b ON (a.Player = b.Player) AND (a.HomeRuns <= b.HomeRuns)
GROUP BY a.Player, a.Year, a.Team, a.HomeRuns
HAVING a.HomeRuns = MAX(b.HomeRuns)
AND ((COUNT(b.Year) = 1) OR (a.Year = MAX(b.Year)))

I guess it's a toss-up as to which someone fight find more intuitive and easier to read.
3/29/2007 12:41 AM | Rick O
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

Thanks, Rick O, that's quite good, I didn't think of that one. An INNER JOIN is actually all you need there, and in fact putting criteria on outer table (in the HAVING clause) turns the OUTER JOIN into an INNER one anyway.

You can also do it with NOT EXISTS(..) and NOT IN(...) as well.
3/29/2007 8:11 AM | Jeff
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

Hi Sir,

Nice information, but if we check the performance we use without rank and partion function query will take a lot of cost.

9/13/2007 3:02 AM | Abhineet
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

SQL Server 2005: Using PARTITION and RANK in your criteria
10/8/2007 3:37 AM | sachin wandhare
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

Hi Jeff,
Thanks a Lot.
Explanation of Rank and Partition is Great. this is what iam looking for.

Hi Ricko : thanks for the alternate query that is written with out using the rank and partition which is very use ful when we are writing queries that are compatible with sql server 2000.

12/4/2007 7:55 AM | Ajay
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

Another alternative could be this:


SELECT
B.*
FROM
Batting B
INNER JOIN
(
SELECT B.Player, MAX(B.[Year]) AS MaxYear
FROM Batting B
INNER JOIN
(
SELECT Player, MAX(HomeRuns) AS MaxHomeRuns FROM Batting GROUP BY Player
) R ON R.Player = B.Player AND R.MaxHomeRuns = B.HomeRuns
GROUP BY B.Player
) R ON R.Player = B.Player AND R.MaxYear = B.[Year]


This may appear more complicated than the previous two versions at first. The method to this approach is fairly straight forward:

This uses two nested selects. The deepest select determines the maximum number of home runs for each player. This information is then used to determine the highest year that each player achieved those maximum home runs. This player/year primary key composition is then used to retrieve the entire record from the table.

In my opinion when choosing which method to use, performance is a key factor to be considered.

I ran a series of tests where all three methods are executed in sequence. The execution plans indicate the relative costs of each method. 5 tests were run, initially for 13 records, and then incremented by 13 thereafter. The outcome was as follows:

A - Partition Method
B - Rick O's Method
C - My Method

Recs A B C
13 34% 18% 25%
26 26% 19% 19%
39 21% 21% 15%
52 18% 22% 13%
65 16% 23% 11%

(Note: The initial INSERT statements account for the remaining cost)

Draw your own conclusion from this.





8/4/2008 4:42 AM | Jon Whalley
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

Nice article Jeff!
Thanks a lot...
8/13/2008 11:38 AM | Chamila
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

I came up with something you can do with Rank/partition that is very difficult otherwise...

Consider a table that contains the columns "Entity","Rating","Date" representing the rating of an entity on a certain day
I wanted to create a table that had the columns
"Entity","Rating","From date","To date" where it summarized the rows into blocks.

The issue is that I wanted to store a single row of data for each change in rating over time, but without losing the contiguous nature of the data, in this case Rating of a company that may change from AAA to AA for a week and then go back to AAA.

If I grouped by rating, I lost the contiguous dates, but couldn't group by a date range easily, so I was left scratching my head as to what to do.

I figured that by creating two fields,
one
rank() over (partition by Entity order by date)
rank() over (partition by Entity,Rating order by date)

The first gives me sequential numbers for each of the rows for an entity
The second gives me sequential numbers for each of the rows for an entity+rating

so for this data
(1,AAA,1/1/2008)
(1,AAA,2/1/2008)
(1,AAA,3/1/2008)
(1,A,4/1/2008)
(1,A,5/1/2008)
(1,AAA,6/1/2008)
(1,AAA,7/1/2008)
(1,AAA,8/1/2008)

I would get
(1,AAA,1/1/2008,1,1)
(1,AAA,2/1/2008,2,2)
(1,AAA,3/1/2008,3,3)
(1,A,4/1/2008,4,1)
(1,A,5/1/2008,5,2)
(1,AAA,6/1/2008,6,4)
(1,AAA,7/1/2008,7,5)
(1,AAA,8/1/2008,8,6)

As you can see the last column carries on incrementing the next block of AAA where the last AAA left off (1.2.3......4.5.6)

I figured by subtracting one from the other, I get a number that stays the same as long as the rows are contiguous, hence gives me a means to aggregate to find the max and minimum

The full code follows for those interested


create table #temp (Entity varchar(10),Rating varchar(10),date smalldatetime)

insert into #temp values ('1','AAA','2008/01/02')
insert into #temp values ('1','A','2008/01/03')
insert into #temp values ('1','A','2008/01/04')
insert into #temp values ('1','AAA','2008/01/05')
insert into #temp values ('1','AAA','2008/01/06')
insert into #temp values ('1','A','2008/01/07')
insert into #temp values ('1','AAA','2008/01/08')
insert into #temp values ('1','A','2008/01/09')
insert into #temp values ('1','AAA','2008/01/10')

select Entity,Rating,min(date) as 'Valid_From',max(date) as 'Valid_To'
from
(
select
*,
rank() over (partition by Entity,Rating order by date) -
rank() over (partition by Entity order by date) as delta
from
#temp
) as x
group by
Entity,Rating,delta
order by Entity,Valid_From

I ran it against a pretty large data set, and let's just say it's VERY fast- summarizing deltas for around 400 000 records over a year in around 12 seconds without indexes...

Run the query in the brackets separately to see what the rank does. Note that this rank is only for use in grouping by Entity AND Rating.







8/19/2008 2:20 PM | Mike Renwick
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

Jon Whalley wrote:

Recs A B C
13 34% 18% 25%
26 26% 19% 19%
39 21% 21% 15%
52 18% 22% 13%
65 16% 23% 11%

"Draw your own conclusion from this."
...

Sure, I'd draw my own conclusion if that table made any sense, which it doesn't. Percent of what? Costs are what? The costs could be anything from processor load to time in milliseconds to pleasure derived from each method. My conclusion is that your results don't tell me anything at all.
8/26/2008 2:44 PM | Matt
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

"Sure, I'd draw my own conclusion if that table made any sense, which it doesn't. Percent of what? Costs are what? The costs could be anything from processor load to time in milliseconds to pleasure derived from each method. My conclusion is that your results don't tell me anything at all."

Erm

If you look at a SQL execution plan (hover over a step) and you will see a percentage cost of a query. John Whalley is making the legitimate observation that his approach is substantially more efficient than using rank(), partition or the "B" recommendation.

The percentage cost is the proportionate cost of a step in a query, or a query in a batch of queries within a stored procedure or other t-sql.

Your criticism is both unfounded and betrays an ignorance of SQL Server. "pleasure derived from each method"? just listen to yourself.

"My conclusion is that your results don't tell me anything at all."

My conclusion is you should read-up first before posting criticisms of things you don't understand.
9/12/2008 7:57 AM | dasv
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

Great article, Jeff!

A simple example with a clear explanation.
Thanks!
1/30/2009 12:03 PM | mca
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

can I use aggregate functions within the rank function? for example, can i do this? Don't worry about the column names. Just wanted to know if I can use an aggregation function in the rank function :-

rank() over(partition by fc.facilityName order by (sum((sd.unitsSold * sd.salePrice) +(sd.unitsSold * sd.salePrice * sd.taxPercentage/100) ) + sum(sa.shippingCharge)) desc) as rank
2/4/2009 12:50 PM | VK
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

I found this informative. Also, people should know that with large resultsets, using RANK() and PARTITION is much faster than the alternatives.
2/25/2009 4:47 PM | Peg
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

I don't think you need to include the insert in your assesment as to which query is faster or more efficient. If you do a SET STATISTICS PROFILE ON and look at the results of just the select statements, what you will see is that the RANK() performs just as good as the nested derived query test or better in some areas.

I would also state that because the RANK is using partitioning...it is creating it's own clustered index on the fly, which is most likely where all of the CPU cycles are being used. This would be less prevalent on an indexed field.

There are more executes in the derived query method and there are aggregates being performed. In a production environment you want to stay away from aggregates whenever possible...speaking from experience.

Lastly in my humble opinion, readability must come into play here. While the derived query shows enginuity and is not all that difficult to read on such a simple example, I would imagine on a more complex example with many more joins involved it will become more difficult for someone to understand, while the RANK() query maintains a degree of elegance and simplicity that lends itself to maintainability.

I give the RANK() query an A+ and the Derived an A. Nice work everyone and a good discussion.
3/9/2009 9:32 AM | Eric
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

Thank you for this. It got me going with RANK() fairly quickly. Well explained!
8/5/2009 4:11 PM | Dave
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

thank you this is great!, quick question can you use RANK() in any edition of sql server 2005?
8/13/2009 11:44 AM | Rosie
Gravatar

# Alternative

In some database like Teradata, you could have done :

select Player, Year, HomeRuns, Rank() over (Partition BY Player order by HomeRuns DESC, Year DESC) as Rank
from Batting
qualify Rank = 1

But "Qualify" doesn't exist in SQL Server !


9/10/2009 9:15 AM | Krp
Gravatar

# SQL Server 2005: Using PARTITION and RANK in your criteria

this is good article. i found this through Google.
10/3/2009 3:42 AM | john
Gravatar

# SQL Server 2005: Using PARTITION and RANK in your criteria

hi this is a good article, to know about more examples on sql server 2008 rank function please check it out the following link. i hope it will help u lot. SQL Server 2008 Rank Function
10/3/2009 3:46 AM | valli
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

Its a very nice article.

Thanks.

Can we go for this query,

select b.Player,max(b.Year) Year,b.Team,b.HomeRuns from batting b
inner join (
select a.Player,max(homeruns) as HomeRuns
from batting a group by player) t
on b.player=t.player and b.homeruns=t.homeruns
group by b.Player,b.Team,b.HomeRuns
10/19/2009 9:24 AM | Kumar Rajan
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

dasv wrote: [John Whalley is making the legitimate observation that his approach is substantially more efficient than using rank(), partition or the "B" recommendation.
Your criticism is both unfounded and betrays an ignorance of SQL Server. "pleasure derived from each method"? just listen to yourself.
My conclusion is you should read-up first before posting criticisms of things you don't understand.]

create function dbo.theworsefunctionever()
returns bit
as
begin
declare @result bit
select @result = 1
from sys.columns as a
cross join sys.columns as b
cross join sys.columns as c

return(@result)
end
go

--now execute
--the following shows a query cost of 100% and finishes execution in less than 1 sec
select * from sys.columns
where system_type_id = 127
and max_length = 8

--the following shows a query cost of 0% and finishes execution in more than 10 secs
select dbo.theworsefunctionever()


Based on the execution percentage cost [as dasv wrote:]the "LEGITIMATE" result is that the second query is "substantially more efficient"

smattering is dangerous, right dasv?

11/24/2009 12:24 PM | theworm
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

A comparison of execution plan output is meaningless if the candidate queries are doing something completely different.

But if you compare the execution plans for two queries that produce the same result set, as John Whalley was, then it is very meaningful.
11/25/2009 7:24 AM | nommis
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

nommis you could have a look at the following link:
http://stackoverflow.com/questions/564717/measuring-query-performance-execution-plan-query-cost-vs-time-taken
if you want to compare statements based on their relative cost, then it is your choice as long as you can justify it and support it.
Btw, have you ever seen any serious "post" showing comparison results in relative costs ?
11/25/2009 11:54 AM | theworm
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

8/19/2008 2:20 PM | Mike Renwick

..."I figured by subtracting one from the other, I get a number that stays the same as long as the rows are contiguous, hence gives me a means to aggregate to find the max and minimum"...


Mike,
Your post regarding creating the delta column was very insightful and helped solving a similar problem.

Thanks.

12/30/2009 3:55 PM | rd
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

Any ideas on using this in MS Access?
1/19/2010 2:15 PM | Luna
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

Hi jeff,

Great...

I was struggling with some very complicated queries and this article gave me clear understanding about ranking.
can you please send another article or example for dense_rank.

Thanks in advance.
2/3/2010 7:22 AM | Vaibhav
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

This is brilliant!
2/10/2010 5:12 PM | Aaron
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

Here's on for you. I ran the following statement dozens of times and it worked great. Then, I changed the where clause and it stopped working. Any thoughts? I'm getting an error "MSG 258, Level 15, State 1, Line 3 Cannot call methods on decimal."

SELECT * FROM
(
SELECT H.location_id. L.item_id AS po_item, H.po_no, H.order_date, H.created_by,
RANK() OVER (PARTITION BY H.location_id, L.item_id ORDER BY H.location_id, L.item_id, H.order_date DESC) AS Rank
FROM p21_view_po_hdr H INNER JOIN p21_view_po_line L
ON H.po_no = L.po_no
) tmp
WHERE Rank = 1 AND po_item = '6G-6MP'

Like I said, it worked great for about 1 dozen tries then it just stopped working.
2/10/2010 5:37 PM | Aaron
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

You know, sometimes if you just read. I missed my type-o on the 1st line. Genius.
2/10/2010 5:38 PM | Aaron
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

if i wan t to be in format as


player year team ..............................

A 2001
2002
2003
2004
2005
b 2001
2002
2003
2004
i am not able to do as one to many as above


2/25/2010 3:43 AM | jayanti
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

I just now came across this older article, but what a great explanation. I'm actually using an internal query builder package and it uses the Rank/Partition method and doesn't include any rownum, top 1 type of functions, so this explanation was great and I was quickly able to build a test query outside of the query builder that is actually faster than my test original query using a derived table to return a rownum from. Great stuff!
4/1/2010 10:32 AM | Del Duncan
Gravatar

# re: SQL Server 2005: Using PARTITION and RANK in your criteria

Good article.

We have a few packages that have case sensitive databases... any chance you could make my life easier the next time!
4/1/2010 2:58 PM | Shane
Gravatar

# Mr

Hi Jeff,

Could you show me how to get median (by using PARTITION and RANK)?

Thanks
Alvin
10/6/2010 2:18 PM | Alvin
Gravatar

# Finding last 3 pay dates using PARTITION and RANK in your criteria

I'm trying to create a query that displays the last 3 payment dates of our customers. I'm trying the following and I am getting an error message for an invalid identifier. Can you show me where I've made my mistake?

Thanks in advance,

Osei

SELECT
L.CUST_NUMBER AS ACCT_ID, C1.CUST_LNAME
MAX(CASE WHEN DATE_RANK = 3 THEN C.CASH_DRWR_DATE END) AS THIRD_TO_LAST_PMT_DATE,
MAX(CASE WHEN DATE_RANK = 2 THEN C.CASH_DRWR_DATE END) AS SECOND_TO_LAST_PMT_DATE,
MAX(CASE WHEN DATE_RANK = 1 THEN C.CASH_DRWR_DATE END) AS LAST_PMT_DATE
FROM
(SELECT UNIQUE
L.CUST_NUMBER AS ACCT_ID, C1.CUST_LNAME, C.CASH_DRWR_DATE,
DENSE_RANK() OVER (PARTITION BY L.CUST_NUMBER ORDER BY C.CASH_DRWR_DATE DESC) AS DATE_RANK
FROM
AMSUSER.LOAN L
LEFT OUTER JOIN AMSUSER.CUSTHIST C ON L.CUST_NUMBER = C.CUST_NUMBER
LEFT OUTER JOIN AMSUSER.CUSTOMER C1 ON L.CUST_NUMBER = C1.CUST_NUMBER
WHERE L.LOAN_TYPE <> 'IS' AND
L.LOAN_STATUS IN ( 'O')) X
GROUP BY L.CUST_NUMBER
ORDER BY C1.CUST_LNAME ASC
10/7/2010 10:19 AM | Osei
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET