We can’t reference the result of an aggregate function (for example MAX() ) in a WHERE clause of the same SELECT. The normative pattern for solving this type of problem is to use an inline view, something like this: SELECT t.firstName , t.Lastname , t.id FROM mytable t JOIN ( SELECT MAX(mx.id) AS max_id FROM … Read more
I do it this way: SELECT e.*, s1.score, s1.date_added FROM entities e INNER JOIN scores s1 ON (e.id = s1.entity_id) LEFT OUTER JOIN scores s2 ON (e.id = s2.entity_id AND s1.id < s2.id) WHERE s2.id IS NULL;
this will give you result that has the minimum price on all records. SELECT * FROM pieces WHERE price = ( SELECT MIN(price) FROM pieces ) SQLFiddle Demo
There are 2 solutions: 1.sort_values and aggregate head: df1 = df.sort_values(‘score’,ascending = False).groupby(‘pidx’).head(2) print (df1) mainid pidx pidy score 8 2 x w 12 4 1 a e 8 2 1 c a 7 10 2 y x 6 1 1 a c 5 7 2 z y 5 6 2 y z 3 3 … Read more
Using a self join: This will return all the values with subkey values that match, in case there are multiples. SELECT a.value FROM TABLE a JOIN (SELECT MAX(t.subkey) AS max_subkey FROM TABLE t WHERE t.key = 1) b ON b.max_subkey = a.subkey WHERE a.key = 1 Using RANK & CTE (SQL Server 2005+): This will … Read more
Try using MAX with a GROUP BY. SELECT u.userName, MAX(c.carPrice) FROM users u LEFT JOIN cars c ON u.id = c.belongsToUser WHERE u.id = 4; GROUP BY u.userName; Further information on GROUP BY The group by clause is used to split the selected records into groups based on unique combinations of the group by columns. … Read more
The syntax for index hints is documented here: http://dev.mysql.com/doc/refman/5.6/en/index-hints.html FORCE INDEX goes right after the table reference: SELECT * FROM ( SELECT owner_id, product_id, start_time, price, currency, name, closed, active, approved, deleted, creation_in_progress FROM db_products FORCE INDEX (products_start_time) ORDER BY start_time DESC ) as resultstable WHERE resultstable.closed = 0 AND resultstable.active = 1 AND resultstable.approved … Read more
One way to do this that correctly uses group by: select l.* from table l inner join ( select m_id, max(timestamp) as latest from table group by m_id ) r on l.timestamp = r.latest and l.m_id = r.m_id order by timestamp desc How this works: selects the latest timestamp for each distinct m_id in the … Read more
Try this: Select u.[username] ,u.[ip] ,q.[time_stamp] From [users] As u Inner Join ( Select [username] ,max(time_stamp) as [time_stamp] From [users] Group By [username]) As [q] On u.username = q.username And u.time_stamp = q.time_stamp