Join vs. sub-query I am an old-school MySQL user and have always preferred JOIN over sub-query. But nowadays everyone uses sub-query and I hate it, I don't know why. I lack the theoretical knowledge to judge for myself if there is any difference. Is a sub-query as good as a JOIN and therefore there is nothing to worry about? Answer: Taken from the manual (14.2.10.11 Rewriting Subqueries as Joins):       A LEFT [OUTER] JOIN can be faster than an equivalent subquery because the server      

How can I SELECT rows with MAX(Column value), DISTINCT by another column in SQL? My table is: id home datetime player resource ---|-----|------------|--------|--------- 1 | 10 | 04/03/2009 | john | 399 2 | 11 | 04/03/2009 | juliet | 244 5 | 12 | 04/03/2009 | borat | 555 3 | 10 | 03/03/2009 | john | 300 4 | 11 | 03/03/2009 | juliet | 200 6 | 12 | 03/03/2009 | borat | 500 7 | 13 | 24/12/2008 |

SQL Select only rows with Max Value on a Column I have this table for documents (simplified version here): +------+-------+--------------------------------------+ | id | rev | content | +------+-------+--------------------------------------+ | 1 | 1 | ... | | 2 | 1 | ... | | 1 | 2 | ... | | 1 | 3 | ... | +------+-------+--------------------------------------+ How do I select one row per id and only the greatest rev?With the above data, the result should contain two rows: [1, 3, ...] and [2, 1, ..].

How to Delete using INNER JOIN with SQL Server? I want to delete using INNER JOIN in SQL Server 2008. But I get this error,          Msg 156, Level 15, State 1, Line 15         ncorrect syntax near the keyword 'INNER'. My code: DELETE FROM WorkRecord2 INNER JOIN Employee ON EmployeeRun=EmployeeNo WHERE Company = '1' AND Date = '2013-05-06' Answer : You need to specify what table you are deleting from, here is a version with an alias: DELETE w

Select first row in each GROUP BY group? As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; id | customer | total ---+----------+------ 1 | Joe | 5 2 | Sally | 3 3 | Joe | 2 4 | Sally | 1 I'd like to query for the id of the largest purchase (total) made by each customer. Something like this: