0

Here is my example

sqlfiddle

My data (table_1)

col_1
foo
foo
bar
bar

My queries:

select col_1, rank() over(order by col_1) as rank from table_1;

returns: 

col_1 rank
foo    1
foo    1
bar    3
bar    3

and this query returns

select col_1, rank() over(order by col_1) as rank from table_1 group by col_1

col_1 rank
foo    1
bar    2

So if I understand correctly rank is executed only after group by clause and order next to rank is also only executed after group by clause.

Update

In case SQLFiddle does not run, here is query I used to create table:

CREATE TABLE table_1  
    (
     col_1 varchar(30)
    );

INSERT INTO table_1
(col_1)
VALUES
('foo'),
('foo'),
('bar'),
('bar')
;
user1700890
  • 7,144
  • 18
  • 87
  • 183
  • 1
    No it doesn't work like that. Are you sure you got those results in SQLFiddle? (not accessible for me). It should do the ranking by ordering on col_1. That is bar would be 1 and foo 3 in first and bar:1 foo:2 in second. Maybe you used desc. In second one grouping is done and then the select (as with any other select). – Cetin Basoz Oct 02 '18 at 19:36
  • @CetinBasoz, Thank you for reply! Yes, this is how it works in SQLFiddle, and I am a bit puzzled, I was expecting `rank` and `order` to be always executed prior to `group by`. SQLFiddle works for me, just takes a minute to load. – user1700890 Oct 02 '18 at 19:40
  • that sqlfiddle link crashed for me also. Can you just post the details here? – Sean Lange Oct 02 '18 at 19:40
  • 2
    just `SELECT` is executed after `GROUP BY`, you can google `actual execution order in sql server` or here: https://stackoverflow.com/questions/17403935/what-is-the-order-of-execution-for-this-sql-statement – LONG Oct 02 '18 at 19:42
  • @SeanLange There are no details to post, except you need to create table_1 shown in the post. (create and insert...). Oh, one thing, this is sql server 2017 – user1700890 Oct 02 '18 at 19:42
  • Why is it so hard to post the table and data here? You already did it once right? It is copy and paste for you. – Sean Lange Oct 02 '18 at 19:46
  • @SeanLange, I just updated post with `create` and `insert` statement. Now post has everything that SQLFiddle has. I am not sure if my update is helpful, but here it is anyway. – user1700890 Oct 02 '18 at 19:50
  • 2
    `So if I understand correctly rank is executed only after group by clause` - your understanding is correct, but what is your question? `rank()` is a window function, those by design are calculated over the final resultset, not over the original one. That is why they may only appear in `select` and cannot appear in `where`. `and order next to rank is also only executed after group by clause` - that is also correct, but your example result is not correct, you could not get `1` against `foo` and `2` against `bar`, it is the other way round, unless you used `order by col_1 desc`. – GSerg Oct 02 '18 at 19:53
  • I am guessing that what you want as a result is bar 1, foo 3. Only those two rows. Just add a distinct to your first query. – Sean Lange Oct 02 '18 at 19:57
  • 1
    There is no question I think, or the question is why SQLFiddle result is wrong. Interestingly it opened quite easily now and the result is correct unlike what you told us it returned as a result. SQLFidlle correctly lists bar first foo second. And if this is a question, as I said in my first reply, in SQL group by executes before the select. – Cetin Basoz Oct 03 '18 at 11:08

0 Answers0