Indeed. The point is to get the list of employeenames based on the effective date (so get the most up to date row not in the future). The query i provided works, and there's no way that i know of to do it more effectively. Your query would require a second roundtrip to the database to get all the names for the found dates and that's what i want to avoid because we use this technique also with sets of hundreds of thousands of rows.
Anyone else who has a guess how to rewrite this using the DAL? Is there any way to alias and subquery? Or can raw sql be added to the where clause? Thanks Op donderdag 30 augustus 2012 11:02:02 UTC+2 schreef Niphlod het volgende: > > I think the point here is retrieve only the record with the same > employeeid that has the max date. if so, why can't we rewrite that ugly > query as (a much more performant) > > SELECT > EmployeeID, > MAX(effdt) > FROM person > WHERE (effdt < now) > GROUP BY EmployeeID > > ? it's because you need the name ? > > Il giorno giovedì 30 agosto 2012 08:32:21 UTC+2, Johann Spies ha scritto: >> >> Hallo Remco, >> >> >> Try this: >> import datetime >> now = datetime.datetime.now() >> query = db.person.effdt > now >> data = SQLFORM.grid(query) >> >> Regards >> Johann >> >> -- >> Because experiencing your loyal love is better than life itself, >> my lips will praise you. (Psalm 63:3) >> >> --
