New topic: SQL query
<http://forums.realsoftware.com/viewtopic.php?t=31390> Page 1 of 1 [ 2 posts ] Previous topic | Next topic Author Message brisance Post subject: SQL queryPosted: Thu Dec 03, 2009 1:11 am Joined: Tue Oct 06, 2009 2:38 am Posts: 20 Need some help writing a SQL query. There's a table with columns of employee id, hours worked and date of entry. I wish to get the top 5 employees that worked the most hours between 2 dates. e.g. let's say Employee A worked a total of 50 hours between a 40 day period, and I wish to return all rows that make up these 50 hours for Employee A and the other 4 employees who worked the most hours. In other words, the first entry should be the employee (group by employee id) who worked the most hours followed by a bunch of rows in descending order (order by hours worked). Repeat this for the other 4 employees. How would I go about this? Top dglass Post subject: Re: SQL queryPosted: Thu Dec 03, 2009 1:51 am Joined: Fri Sep 30, 2005 9:29 am Posts: 666 Location: California Code:SELECT employeeID, hoursWorked, dateOfEntry FROM employeeActivity WHERE employeeID IN (SELECT employeeID, SUM(hoursWorked) FROM employeeActivity WHERE dateOfEntry >= StartDate AND dateOfEntry <= EndDate ORDER BY SUM(hoursWorked) DESC LIMIT 5) ORDER BY employeeID ASC, hoursWorked DESC Just off the top of my head, so it may not be exactly right. However, if this is for SQLite/REALSQLDatabase then you'll have to break it into two queries (one to get the top 5 workers (employeeID, most likely), and the second to get the hoursWorked records) because SQLite doesn't accept LIMIT in a compound query unless it is in the last SELECT. Not sure if other DB engines have similar restrictions on the use of LIMIT. Top Display posts from previous: All posts1 day7 days2 weeks1 month3 months6 months1 year Sort by AuthorPost timeSubject AscendingDescending Page 1 of 1 [ 2 posts ] -- Over 1500 classes with 29000 functions in one REALbasic plug-in collection. The Monkeybread Software Realbasic Plugin v9.3. http://www.monkeybreadsoftware.de/realbasic/plugins.shtml [email protected]
