Is there a rank over function in SPSS using SQL?

Moderators: statman, Analyst Techy, andris, Fierce, GerineL, Smash

cosi
Posts: 4
Joined: Fri Dec 15, 2017 3:28 am

Is there a rank over function in SPSS using SQL?

Postby cosi » Fri Dec 15, 2017 5:43 am

Hi

I have trying to run this code below :

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 c
ON
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 = 1
ORDER BY od.month;

But the results showed SQL error " FROM keyword is not found". Although I have changed the rank over syntax a few times.
I am wondering, does SPSS allows the use rank over function?

Thanks!
JonPedersen
Posts: 119
Joined: Wed May 25, 2011 7:07 am
Contact:

Re: Is there a rank over function in SPSS using SQL?

Postby JonPedersen » Fri Dec 15, 2017 10:48 am

Hi,
In principle SPSS should support whatever is supported by the SQL-database in question for the select. (at least according to the vs 23 documentation for get capture and get data)
But you may have a problem in your 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
should perhaps be
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

at least on MS-SQL 2008+
(note also the comma)
hth Jon
cosi
Posts: 4
Joined: Fri Dec 15, 2017 3:28 am

Re: Is there a rank over function in SPSS using SQL?

Postby cosi » Fri Dec 15, 2017 1:04 pm

Hi Jon,
Thanks for your reply. Before I put in the Rank over syntax, I managed to run the query smoothly without the comma
But after input this rank over function, I am having multiple error problem for example : " Like is keyword is not found" which I resolve by insert the Rank over synatx under where clause.
But another error hit which mentioned SQL command is not ended properly - ORAC 00933 leads me to think does rank over can be use in spss.
cosi
Posts: 4
Joined: Fri Dec 15, 2017 3:28 am

Re: Is there a rank over function in SPSS using SQL?

Postby cosi » Wed Dec 20, 2017 3:06 pm

Hi
I have managed to resolve the issue. It is due to the syntax error that cause the script to fail. The rank over function works wonderfully fine!

Thanks for helping!

Who is online

Users browsing this forum: No registered users and 1 guest

cron