-1

In the code below, is SecondHighestSalary a column name or the name of a table? If it's the name of a table, is it a temp table or an alias, or something else?

select ( select distinct salary from employee order by salary desc 
offset 1 rows fetch next 1 rows only ) SecondHighestSalary;

Another example is below. What is e? Alias, table, column name, or something else?

select Max(Salary) as SecondHighestSalary from
(select Salary,
             rank() over (order by Salary desc) as 'rank' 
      from Employee
     ) e
where rank = 2
Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
itstoocold
  • 2,385
  • 2
  • 12
  • 15
  • Certainly `SecondHighestSalary` is an alias in both the examples above as is `e` in the second example. The use of ' to delimit an alias as in `'rank'`is unusual and my be depricated. – Peter Smith Jul 30 '19 at 14:47
  • And e is an alias for the subquery in the second example. But both of these queries seem to be way overly complicated for what they are doing. – Sean Lange Jul 30 '19 at 14:48
  • To clarify... `SecondHighestSalary` is an alias for the column in both queries. – SS_DBA Jul 30 '19 at 14:50

2 Answers2

0

"SecondHighestSalary" is a column name. "e" is a derived table name.

Chris Hackett
  • 399
  • 1
  • 9
  • Why do you need to name the table/query 'e' to select from it? – itstoocold Jul 30 '19 at 15:19
  • You need some table name to reference it. It could be e, MyTable, etc... – Chris Hackett Jul 30 '19 at 15:23
  • OK. I would have thought you would be able to just select from the result of another select query... is this not the case across not just SQL Server but all other SQL databases in general? – itstoocold Jul 30 '19 at 15:33
  • @itstoocold, you have to name the table because that is part of the requirements of the language, that all tables have names. In this case, the table could be anonymous, but that functionality hasn't been implemented by database vendors. I often reflect that cascading of various operations in SQL is very long-winded and requires unused aliases to be provided, but that unfortunately is just the way it is currently. – Steve Jul 30 '19 at 15:35
  • Why isn't naming the select query needed in this problem, for Microsoft SQL Server? – itstoocold Jul 30 '19 at 17:55
  • select customers.name as 'Customers' from customers where customers.id not in ( select customerid from orders ) – itstoocold Jul 30 '19 at 17:55
  • itstoocold because that is not a derived table - you are not selecting from ( select customerid from orders ) as a table, you are using the results as a list of values. – Chris Hackett Jul 31 '19 at 12:06
0

In both examples the SecondHighestSalary is an alias for the column. This avoids the data coming out with (No Column Name)

e is an alias for the table, this is needed so you can reference the table, if this wasn't there the code would error.

ChrisM
  • 505
  • 6
  • 18