CODEWITHSUNDEEP
sql-servert-sql
user2377214
user2377214

Reputation: 27

Some Examples From Ben-Gan Why need "AS"

SELECT MIN(col1) AS start_range, MAX(col1) AS end_range 
FROM (SELECT col1, 
        (
        SELECT MIN(B.col1) 
         FROM dbo.T1 AS B 
         WHERE B.col1 >= A.col1 
           AND NOT EXISTS 
             (SELECT * 
              FROM dbo.T1 AS C 
              WHERE C.col1 = B.col1 + 1)
        ) AS grp
      FROM dbo.T1 AS A) AS jh 
GROUP BY grp;

Why there would be error if I delete "AS jh"? And why I need same "AS grp". (Not same because I group by) but I can`t delete it. In second code no such "AS".

SELECT col1, 
  (SELECT MIN(B.col1) 
    FROM dbo.T1 AS B 
    WHERE B.col1 >= A.col1 
      -- is this row the last in its group? 
      AND NOT EXISTS 
        (SELECT * 
         FROM dbo.T1 AS C 
         WHERE C.col1 = B.col1 + 1))  FROM dbo.T1 AS A;

And third code:

SELECT MIN(col1) AS start_range, MAX(col1) AS end_range 
FROM (SELECT col1,  
        -- the difference is constant and unique per island 
        col1 - ROW_NUMBER() OVER(ORDER BY col1) AS grp 
      FROM dbo.T1) AS D 
GROUP BY grp;

How it works? It shows the same result as first code. I understand second and fourth code. But I can`t really understand third. And my understanding of first is half. How that (third) works? Fourth:

SELECT col1, col1 - ROW_NUMBER() OVER(ORDER BY col1) AS diff 
FROM dbo.T1;

Upvotes: 1

Views: 71

Answers (1)

gbn
gbn

Reputation: 432331

First code: the inline select returns a result set = requires an alias

Second code

  • the EXISTS is a correlated subquery to do a semi-join. No result set
  • it returns a scalar value (not a result set) - no alias needed (but no column name then)

Third code:

  • Uses ROW_NUMBER() which did not exist before SQL server 2005
  • alias needed because it returns a result set

Not sure what you mean after that...

Upvotes: 6

Related Questions