ranking problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

ranking problem

Author

Topic

mr_mist
Grunnio

1870 Posts

Posted - 2003-05-15 : 08:12:15

Hi guys and girls

Not posted for a while so thought I would return with a problem for you, because I'm nice like that.

What I need to do is take the output from the select given here after the DDL

create table households
(householdid int primary key not null,
householdname varchar(20) null)
create table customers
(customerid int primary key not null,
customerhouseid int not null,
customername varchar (20) null,
customerstartdate datetime not null)
create table payments
(customerid int primary key not null,
paymentlevel int not null)
create table ranksperhousehold
(customerid int not null ,
houseid int not null ,
rank int
)

alter table ranksperhousehold
add constraint [pk_rhh] primary key clustered
(customerid, houseid) on [primary]

insert into households values (1,'smith')
insert into households values (2,'jones')
insert into households values (3,'jackson')
insert into households values (4,'meers')

insert into customers values (1,1,'john','19991202')
insert into customers values (2,1,'marie','20000103')
insert into customers values (3,1,'steve','20010303')
insert into customers values (4,2,'rich','20000102')
insert into customers values (5,3,'luke','20010102')
insert into customers values (6,3,'jane','20020203')
insert into customers values (7,3,'sarah','20020203')
insert into customers values (8,4,'lou','19990101')
insert into customers values (9,4,'Albert','20000101')
insert into customers values (10,4,'Alissa','20020505')

insert into payments values (1,70)
insert into payments values (2,12)
insert into payments values (3,89)
insert into payments values (4,78)
insert into payments values (5,83)
insert into payments values (6,33)
insert into payments values (7,88)
insert into payments values (8,38)
insert into payments values (9,84)
insert into payments values (10,75)

--

select
customername, householdname, customerstartdate, paymentlevel
from customers
with (nolock)
inner join households with (nolock)
on customerhouseid = householdid
inner join payments with (nolock)
on payments.customerid = customers.customerid
order by customerstartdate

and add a column to rank people by HOUSEHOLDNAME (or householdid) in order of customerstartdate (earliest first), so that the results look like this

customername householdname customerstartdate paymentlevel rank
lou meers 01/01/99 38 1
john smith 12/02/99 70 1
Albert meers 01/01/00 84 2
rich jones 01/02/00 78 1
marie smith 01/03/00 12 2
luke jackson 01/02/01 83 1
steve smith 03/03/01 89 3
jane jackson 02/03/02 33 2
sarah jackson 02/03/02 88 3
Alissa meers 05/05/02 75 3

I can't figure out how I get the ranking, though I imagine it's something to do with a temporary ranking table and a group by on householdid.

Please help. :)

-------
Moo.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-15 : 09:13:17

How's this:

select
customername, householdname, customerstartdate, paymentlevel,
(select count(*) from customers a where
a.customerhouseId = customers.customerhouseID and
a.customerstartdate <= customers.customerstartdate) as houseRank
from customers
with (nolock)
inner join households with (nolock)
on customerhouseid = householdid
inner join payments with (nolock)
on payments.customerid = customers.customerid
order by customerstartdate

By the way -- your data has at least 1 "tie" in it, which is causing some ranking problems ... jane and sarah. what is the tie breaker, if any?

- Jeff

mr_mist
Grunnio

1870 Posts

Posted - 2003-05-15 : 09:37:37

Yes, I included that on purpose but forgot about it. :D

Your query deals with it quite well, giving the same ranking for both, but, if it were to be tie-broke, then I would think that it would be broke on customer ID. (lowest customer ID wins.)

-------
Moo.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-15 : 10:50:45

Here's your tie-breaker logic, then:

select
customername, householdname, customerstartdate, paymentlevel,
(select count(*) from customers a where
a.customerhouseId = customers.customerhouseID and
(a.customerstartdate < customers.customerstartdate OR
(a.customerstartdate = customers.customerstartdate AND
a.customerID <= customers.customerID))) as houseRank
from customers
with (nolock)
inner join households with (nolock)
on customerhouseid = householdid
inner join payments with (nolock)
on payments.customerid = customers.customerid
order by customerstartdate

a bit more confusing, but hopefully the logic makes sense ....

- Jeff

mr_mist
Grunnio

1870 Posts

Posted - 2003-05-15 : 11:24:48

quote:

a bit more confusing, but hopefully the logic makes sense ....

- Jeff

Yep, that's great thanks.

-------
Moo.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources