Count (*) with order not working on PostgreSQL which works with Oracle
Below is a Sql query that works in oracle but does not work in PostgreSQL.
select count(*) from users where id>1 order by username;
I know this order doesn't make sense in this query, but still why is it working on oracle. Below is the error in PostgreSQL
ERROR: column "users.username" must appear in the GROUP BY clause or be used in an aggregate function
Position: 48
SQLState: 42803
PostgreSQL version 9.6.3
source to share
As you can see from the Oracle execution plan , no sorting after row sorting is performed, which indicates that the Oracle SQL Server has implemented ignoring this phrase.
Why doesn't it work in PostgreSQL - because people working with Postgres know what they are doing;) Just kidding, but this question would be very speculative to me without seeing the Oracle and MySQL source. The big questions are if Oracle and MySQL allow it by coincidence, or because Oracle owns both.
Final note: If you are asking why such software applications behave differently, I think it is also important to include the version you are talking about. Even different versions of the same app can follow different instructions.
source to share
If you are only looking for the count of all records then there is no need for order by
it because it doesn't even make sense in oracle. In this case, delete order by
.
select count(*) from users where id>1
If you are looking for a wise count username then it makes sense to sort by username, in which case you can use the following query.
select count(*) from users where id>1 group by username order by username;
Hope your doubts will be cleared up.
source to share