HQL using alias in order by

[Sumit Verma]

Seems like there is no way to use alias in order by (or in having
clause). So, if you want to do something like:

SELECT count(foo) AS totalFoo FROM bar ORDER BY totalFoo

It won't work. You can do ORDER BY count(foo) instead, but that's not
very efficient.

Thoughts?

--
Sumit

[Barney Boisvert]

Why isn't it efficient? I would be shocked if any major RDBMS
double-executed the COUNT. The DB pretty much never executes the SQL
as written; query optimizers make - often significant - changes so
things are as efficient as possible. If you're using a database that
gives you detailed view into the actual execution plan for a given SQL
statement you can check it out, but not DBs provide that level of
introspection.

cheers,
barneyb

> --
> You received this message because you are subscribed to the Google Groups "cf-orm-dev" group.
> To post to this group, send email to cf-or...@googlegroups.com.
> To unsubscribe from this group, send email to cf-orm-dev+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/cf-orm-dev?hl=en.
>
>

--
Barney Boisvert
bboi...@gmail.com
http://www.barneyb.com/

[Sumit Verma]

Count is a simple example. But try using (sudo):

select deptname,
(select firstnme + ' ' + lastname
from employee x
where x.empno = y.mgrno) as managerName
from department y
order by managerName

The only solution is to repeat the whole subquery in order by again. I
haven't looked at performance, so, can't talk about that, but just
from ease of use, it's not very friendly.

Since alias works every where else, why not here? Seems like a bug to
me...

> bboisv...@gmail.comhttp://www.barneyb.com/

[Barney Boisvert]

You can do that this way:

select deptname, managername
from (

select deptname,
(select firstnme + ' ' + lastname
from employee x
where x.empno = y.mgrno) as managerName
from department y

) t
order by managername

Or this way:

select deptname, firstname + ' ' + lastname as managername
from department d inner join employee e on d.mgrno = e.empno
order by e.firstname, e.lastname

Or you could combine the two:

select *
from (
select deptname, firstname + ' ' + lastname as managername
from department d inner join employee e on d.mgrno = e.empno
) t
order by managername

Structured Query Language, emphasis on the Structured.

I'm not sure that standard SQL supports aliases in the order by and
having clauses. In fact, I'm pretty sure that it doesn't, though many
(most?) engines do support it. Hibernate is built atop JDBC and
standard SQL, so while this might be a desirable feature, it's not
really a Hibernate bug, but a problem with the SQL standard.
Something like this could probably be expressed via Dialect
implementations, but the way Dialects worked last time I checked
(which, I'll admit, was several years ago) isn't even close to rich
enough to handle this sort of platform-specific syntax. They only
provided direct substitutions for different constructs, not the
ability to transform complex SQL statements.

cheers,
barneyb

[Sumit Verma]

I didn't know that alias was not standard SQL! I have used it for as
long as I can remember, with multiple DB's.

I'm glad I asked as I learned a new way of writing SQL. I didn't know
I could do #1 in your example.

Thanks!

[Barney Boisvert]

Alias in SELECT and FROM is standard, but to my knowledge, aliases in
the ORDER BY and HAVING clauses are not.

cheers,
barneyb

On Mon, Mar 22, 2010 at 12:39 PM, Sumit Verma <su...@blogonria.com> wrote:
> I didn't know that alias was not standard SQL! I have used it for as
> long as I can remember, with multiple DB's.
>
> I'm glad I asked as I learned a new way of writing SQL. I didn't know
> I could do #1 in your example.
>
> Thanks!
>