SQL Anywhere database - how to find out the rank in one sql statement?

Author

how to find out the rank in one sql statement?

Marten Lehmann

2005-06-28, 7:23 am

Hello,

I have question concering an SQL statment: Imagine the following table
structure:

cust_id int
item varchar
price double

Lets put the following data in it:

cust_id item price
1 pant 10.00
1 hat 2.00
2 pant 12.00
2 top 9.00
3 top 15.00
3 hat 3.00

Now I'm looking for an sql-statement, which tells me the rank of the
items based on the price and customer:

select ??? from items where cust_id = 1 and ??? order by cust_id, item

cust_id item rank
1 pant 1
1 hat 1

select ??? from items where cust_id = 2 and ??? order by cust_id, item

cust_id item rank
2 pant 2
2 hat 1

select ??? from items where cust_id = 3 and ??? order by cust_id, item

cust_id item rank
3 top 2
3 hat 2

Do you understand what I'm looking for? I don't want to lookup the items
first and iterate through all customers sort by the price until the
specified cust_id matches. Is there a way in sql to build this within
one statement?

Regards
Marten

Ani Nica

2005-06-28, 9:23 am

You can use the aggregate function rank() if you use ASA 9. Here is query:

create table Prod(
cust_id int,
item varchar,
price double);

insert into Prod values (1, 'pant', 10.00);
insert into Prod values (1, 'hat', 2.00);
insert into Prod values (2, 'pant', 12.00);
insert into Prod values (2, 'top', 9.00);
insert into Prod values (3, 'top', 15.00);
insert into Prod values (3, 'hat', 3.00);

select *
from (select cust_id, item,
rank() OVER (PARTITION BY (item) order by price) as RANK
from prod) as DT
where dt.cust_id = 3
;

--
Ani Nica
Research and Development, Query Processing
iAnywhere Solutions Engineering

EBF's and Patches: http://downloads.sybase.com
choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://casexpress.sybase.com/cx/cx.stm

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288
"Marten Lehmann" <lehmannmapson@cnm.de> wrote in message
news:42c13c5e$1@foru
ms-1-dub...
> Hello,
>
> I have question concering an SQL statment: Imagine the following table
> structure:
>
> cust_id int
> item varchar
> price double
>
> Lets put the following data in it:
>
> cust_id item price
> 1 pant 10.00
> 1 hat 2.00
> 2 pant 12.00
> 2 top 9.00
> 3 top 15.00
> 3 hat 3.00
>
> Now I'm looking for an sql-statement, which tells me the rank of the
> items based on the price and customer:
>
> select ??? from items where cust_id = 1 and ??? order by cust_id, item
>
> cust_id item rank
> 1 pant 1
> 1 hat 1
>
> select ??? from items where cust_id = 2 and ??? order by cust_id, item
>
> cust_id item rank
> 2 pant 2
> 2 hat 1
>
> select ??? from items where cust_id = 3 and ??? order by cust_id, item
>
> cust_id item rank
> 3 top 2
> 3 hat 2
>
> Do you understand what I'm looking for? I don't want to lookup the items
> first and iterate through all customers sort by the price until the
> specified cust_id matches. Is there a way in sql to build this within
> one statement?
>
> Regards
> Marten

Sponsored Links