Rank over error? or other methods?

Question

Rank over error? or other methods?

corgi_77

SSC Enthusiast

Points: 123
More actions
December 14, 2017 at 8:10 pm

#330537

Hello!
I am new to sql and to this forum! I have been trying out this code
"]SELECT
Distinct od.orderdate AS orderdate, od.country AS country, od.month ,c.cust_id as customerid
Rank over () ( Partition by c.cust_id order by od.orderdate DESC) as rank
FROM
order_transaction_table as od INNER JOIN customertable as cON od.cust_id=c.cust_id AND od.country=c.country
WHERE
od.country=2 AND od.month between 201701 and 201711 AND od.valid_order=1 AND c. country = 2 AND c.month between 201701 and 201711 AND c.new member =1 AND c.segment = 1ORDER BY od.month;
I am trying to achieve this result :
I have 2 tables to join, 1 from transactional table and 2nd table is customer table.
I keep on hitting errors such as the "from keywords not found etc."
I am unsure where did I go wrong in the above logic and I also have tried "Select count(c.custid)....." but it fails too.
Thanks in advance for your help!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Thom A SSC Guru Points: 99126 More actions · Answer 1

Rank over () ( Partition by c.cust_id order by od.orderdate DESC) as rank
The above code isn't in the correct format. Have a look at RANK (Transact-SQL), however, that states the correct format is:
RANK ( ) OVER ( [ partition_by_clause ] order_by_clause )

For your code, that means you'll need to do:
RANK([Column To Rank) OVER (PARTITION c.cust_id ORDER BY od.orderdate DESC) AS [rank]

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 2

or maybe just use ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate)

It looks as though it should be ASC rather then DESC sorting? I could, of course, be mistaken - coffee hasn't kicked in yet.

Thomas Rushton
blog: https://thelonedba.wordpress.com

Thom A SSC Guru Points: 99126 More actions · Answer 3

ThomasRushton - Friday, December 15, 2017 3:01 AM
or maybe just use ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate)
It looks as though it should be ASC rather then DESC sorting? I could, of course, be mistaken - coffee hasn't kicked in yet.

No I agree, it did look from the OP's expected result set it should be ASC, not DESC, but I left that be in as it was for now.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

corgi_77 SSC Enthusiast Points: 123 More actions · Answer 4

Thanks for answering call of help! The rank over works wonderfully using ASC. The error I found is due to another syntax error within the scripts.