CODEWITHSUNDEEP
sqlgrouping
Worlds Local Yank
Worlds Local Yank

Reputation: 59

Assistance with SQL Query (Windowing Functions)

Houston Apartment         Order 1
Houston Apartment            Order 5
Houston TownHouse   Order 3
Houston TownHouse   Order 4
Austin  Condo   
Dallas  MultiFamily Order 2

All,

I have a result set like the one above. Using the familiar Customer -> Orders schema as an example, The first two columns (e.g. Houston, Apartment) come from category1 and category2 fields on the Customer Table. The third column comes from the Orders Table and will represent the Primary Key of the table. The values in this column were deliberately listed out of order (1...5...3) to show that I cannot guarantee the order the values.

What I want is to have a column that adds a Rank or Row_number (or calculation?) that numbers each combination of Category 1 and 2:

1   Houston Apartment   Order 1
1   Houston Apartment   Order 5
2   Houston TownHouse            Order 3
2   Houston TownHouse   Order 4
3   Austin  Condo   
4   Dallas  MultiFamily Order 2

So, Houston-Aparment is 1, Houston-TownHouse is 2, etc...

I would like to avoid any sub/nested queries if possible.

Please note: The values in the example or just sample data. The real data is not based on a Customer/orders so I respectfully and humbly ask that you please not chastise me for having Cities and Apartment types as categories, etc (I would put these in separate domain tables in this instance) or suggest a change of schema

Can anyone help please?!

Steve

Upvotes: 0

Views: 58

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269523

Based on the results that you show, I think you want:

select dense_rank() over (order by Category1, Category2) as rankorder, *
from Customers c join
     Orders o
     on o.CustomerID = c.CustomerID

You seem to be adding an index based only on the first two categories, and never starting over again (the partition is used to start counting over again). You have ties with no gaps (1, 1, 2 . . .). For this case, you want dense_rank(). If you had ties with gaps (1, 1, 3 . . .), you would use rank(). If you just wanted the ordering (1, 2, 3) you would use row_number().

Upvotes: 1

Void Ray
Void Ray

Reputation: 10199

Something like this should do:

create table Data
(
    city varchar(50),
    propertyType varchar(50),
    anOrder int
)
insert into Data select 'Houston', 'Apartment', 1
insert into Data select 'Houston', 'Apartment', 5
insert into Data select 'Houston', 'TownHouse', 3
insert into Data select 'Houston', 'TownHouse', 4
insert into Data select 'Austin', 'Condo', 1
insert into Data select 'Dallas', 'MultiFamily', 2

select city, propertyType, RANK() OVER 
    (PARTITION BY Data.city ORDER BY Data.city,Data.propertyType DESC) AS Rank 
from Data
group by city, propertyType

Upvotes: 0

Andomar
Andomar

Reputation: 238068

If your database supports windowing functions, you could use row_number():

select  row_number() over (partition by Category1, Category2 order by CustomerID)
from    Customers c
join    Orders o
on      o.CustomerID = c.CustomerID

Upvotes: 0

Related Questions