SQLTeam.com | Weblogs | Forums

Partition by

If I have this table:

id, value

1 4
2 5
3 5
4 6
5 7
6 7

I'm not able to understand difference about these 2 queries:

SELECT * rank() OVER(order by id )
FROM table

SELECT * rank() OVER( )
FROM table
ORDER BY id

In first case, the "order by" inside "over" clause, permits to order records inside the unique existing partition (because there is no "partition by" command) but why rank() returns always 1 for every records?
How does It understand records have the same value ? I don't specified no field according to evaluate rank.... seraphs It's implicits by "order by" clause?

Both statements have syntax errors. In both you need a comma after the *. RANK() function needs an order by clause in the OVER() clause, so that is another syntax error.

The order by clause in the rank() over() determines how that column is computed. The order by clause at the end determines how the data is presented to you. So for example, this is a valid statement and you will see what it is doing if you run it.

SELECT *, RANK() OVER(ORDER BY id)
FROM table
ORDER BY value desc;