write a query without using a where clause that outputs only positive numbers-CodePudding

everyone we have a table table_num write a query without using a where clause that outputs only positive numbers

CodePudding user response：

Ok, I found a way to do it in Oracle, using the ROWNUM function to give each row in table_num a unique value to group on so I could use the HAVING clause:

select sum(numbers) as numbers
from (select rownum, numbers from table_num)
group by rownum
having sum(numbers) > 0

Some other system besides Oracle might have a different function than ROWNUM, but as long as you can generate a unique value for each row in the subquery, you can group by that value and have access to the HAVING clause.

CodePudding user response：

That's a funny thing, but maybe in future you should better think of more helpful topics. One simple option is to just use HAVING instead of WHERE:

SELECT numbers FROM table_num
GROUP BY numbers
HAVING numbers > 0
ORDER BY numbers;

Another way is using CASE WHEN:

SELECT DISTINCT CASE WHEN numbers > 0 THEN numbers END AS numbers
FROM table_num
ORDER BY numbers;

This will also produce one row with the value NULL for all not matching entries. Maybe this is intended to see whether such entries exist in the table. Remove the DISTINCT in the query above if you really want to output identic numbers multiple times. The first query will show them always only once.