Re: [Haskelldb-users] best way to implement this query?

[Bjorn B. <d00...@dt...>]

Jeremy Shaw wrote:
> Hello,
> 
> I am not quite sure what the best way to do the following in
> haskelldb is:
> 
> Here is the query in SQL:
> 
> select column1 from table1 where column2 = (select max(column2) from 
> table1);
> 
> Basically, I want to find the value in column1 that is in the same
> row as the maximum column2 value. In this case, column2 is a unique
> key.
> 
> I could do this as two seperate queries haskelldb,  one to find the
> max column2 value, and a second query to find the corresponding
> column1 value -- but it would be nice to do it all in one step. I
> imagine I might also be able to hack something up by using sort and
> top -- but that seems a bit hackish when the actual SQL I want to 
> generate is pretty straight forward.
> 
> Any ideas?

One way could be to join the table with itself (the most efficient thing 
in the world, though):

do
   t <- table table1
   t' <- table table1
   r <- project (column2 << _max (t'!column2))
   restrict (t!column2 .==. r!column2)
   project (column1 << t!column1)


I think being able to use queries in expressions is a todo item left over
from the original HaskellDB.

It would be nice if something like this would work:

do
   t <- table table1
   let m = do
                t' <- table table1
                project (column2 << _max (t'!column2))
   restrict (column2 `_in` m)
   project (column1 << t!column1)

That is, we would have a function:

_in :: Eq a => Expr a -> Query (Rel (RecCons f a RecNil)) -> Expr Bool

That doesn't actually work, since the result should be in the Query monad,
but something along these lines should be doable.

/Bjorn