Hello all
I have a "quite simple" SQL query that I would like to propose both as HQL AND Criteria
accessible
Tried several things without any success, looked into the forums without any more luck and googled for more infos on subqueries without being able to find anything which could lead to working code
Could anyone help please
Many thanks
Here is the "beast" which is supposed to return the latest demo and test files in a given directory in one shot
Code:
select db_id,shortname,fullname,file_date from
(select db_id,shortname,fullname,file_date from Files
where folder_id=1 and shortname in ('demo','test')
order by shortname,file_date desc)
as subquery
group by name
which can also be rewritten as
Code:
select db_id,shortname,fullname,file_date from
(select name as xname,max(file_date) as last from Files
where folder_id=1 and name in ('demo','test')
group by name)
as subquery inner join Files as result
where subquery.xname = result.shortname and subquery.last = result.file_date
Login
FAQ
Search
