How to select just one first or last record compliant to a where clause with ScalaQuery?

Question

Having the following query template to select all:

val q = for {
  a <- Parameters[Int]
  b <- Parameters[Int]
  t <- T if t.a == a && t.b == b
  _ <- Query.orderBy(t.c, t.d)
} yield t

I need to modify it to select the very first (with minimum c and d minimum for this c) or the very last (with maximum c and d maximum for this c) record of those matching the where condition. I'd usually strongly prefer no other (than the last/first) records to be selected as there are hundreds thousands of them...

Answer 1

There's a potential danger here in how the OP's query is currently constructed. Run as is, getting the first or last result of a 100K result set is not terribly efficient (unlikely, yes, but the point is, the query places no limit on number of number of rows returned)

With straight SQL you would never do such a thing; instead you would tack on a LIMIT 1

In ScalaQuery, LIMIT = take(n), so add take(1) to get a single record returned from the query itself

val q = (for {
  a <- Parameters[Int]
  b <- Parameters[Int]
  t <- T if t.a == a && t.b == b
  _ <- Query.orderBy(t.c, t.d)
} yield t) take(1)
q.firstOption