0

I created an employee table having attributes : deptno and salary in Oracle DBMS. I executed this query :

SELECT deptno, SUM(salary)

FROM emp
GROUP BY deptno
HAVING 1 > 2 ;

I thought that 1 and 2 are referring to the columns "deptno" and "SUM(salary)" in the SELECT statement. So I put a record where "deptno" > "SUM(salary)" in the table as follows:

deptno          salary
1001            5000
1002            1000

The output is "No rows found" I was expecting the second row as output. Kindly explain the reason.

chris
  • 4,840
  • 5
  • 35
  • 66
A_J
  • 977
  • 4
  • 16
  • 44

2 Answers2

5

1 and 2 is a number. 1 > 2 always returns false. HAVING 1 > 2 means that no matter how many rows in your database, it will always return no rows. It is like you are running while(false) { ... }.

You can use 1 or 2 in ORDER BY to specified which column number to order by. (http://docs.oracle.com/javadb/10.6.2.1/ref/rrefsqlj13658.html), but not with HAVING.

On side note, because you can use column number in ORDER BY, you shouldn't use it. It is not a good practice. Let say that you adding and remove how many column you select. The position of the field you want to order will be adjust. It is better for you to specified it by its name or by its alias. It is more readable as well for other developer to read your code.

invisal
  • 11,075
  • 4
  • 33
  • 54
  • @invisal Makes sense to me!, I would've thought that would of thrown an error. Can you do this in SQL Server? When would you want to do this? – J.S. Orris Jan 23 '15 at 05:00
1

Here 1 & 2 are referred as integers rather than column names. Hence it always gives false values. What you want to do can be done by 

Select deptno,sum(salary)
From emp
Group by deptno
Having deptno>sum(salary);
tharkay
  • 5,913
  • 2
  • 26
  • 33
Kushagra Jain
  • 51
  • 1
  • 6