How to make RANK() or ROW_NUMBER() in ingres/vectorwise ? issue sql code

Question

I am writing a sql query to get sales for different stores on a given day. The query is run against ingres/vectorwise. I want to add a column rank where there is the ranking of the store in regard of sales made in comparaison to all the stores.

My select statement is like follows:

SELECT store_number, sum(sales) as sales
FROM stores_sales_indicators
WHERE day = '2019-07-24'
GROUP BY store_number

I tried different things that I am familiar with from sql-server but none of it worked.

Answer 1

I think this is similar to what you're describing (no day included here but you'll get the idea):

declare global temporary table session.stores_sales_indicators
(
  store_number integer not null,
  sales integer not null
)
on commit preserve rows with norecovery, structure=x100;

insert into session.stores_sales_indicators
values(1,100),(1,200),(2,500),(2,50),(3,50),(3,300);

select
  store_number,
  sum(sales) as sales,
  rank() over (order by sum(sales) desc) as rank
from session.stores_sales_indicators
group by store_number;

See also the fine manual, here's a link to the section on analytic functions: https://docs.actian.com/vector/5.1/index.html#page/SQLLang%2FAnalytical_Functions.htm