without a single primary key field, I think your best bet is: select * from deal_status inner join (select deal_id as did, max(timestamp) as ts from deal_status group by deal_id) as ds on deal_status.deal_id = ds.did and deal_status.timestamp = ds.ts this still won’t work if you allow having two different statuses for the same product … 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
For the sake of completeness, here’s another possible solution: SELECT sensorID,timestamp,sensorField1,sensorField2 FROM sensorTable s1 WHERE timestamp = (SELECT MAX(timestamp) FROM sensorTable s2 WHERE s1.sensorID = s2.sensorID) ORDER BY sensorID, timestamp; Pretty self-explaining I think, but here’s more info if you wish, as well as other examples. It’s from the MySQL manual, but above query works … Read more
DISTINCT is not a function that applies only to some columns. It’s a query modifier that applies to all columns in the select-list. That is, DISTINCT reduces rows only if all columns are identical to the columns of another row. DISTINCT must follow immediately after SELECT (along with other query modifiers, like SQL_CALC_FOUND_ROWS). Then following … Read more
This solution was updated recently. Comments below may be outdated This can query may perform well, because there are no joins. SELECT * FROM ( SELECT *,if(@last_method=method,0,1) as new_method_group,@last_method:=method FROM rpc_responses ORDER BY method,timestamp DESC ) as t1 WHERE new_method_group=1; Given that you want one resulting row per method this solution should work, using mysql … Read more
The columns in the result set of a select query with group by clause must be: an expression used as one of the group by criteria , or … an aggregate function , or … a literal value So, you can’t do what you want to do in a single, simple query. The first thing … 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
You have to use side effecting variables for this SELECT profilename, name FROM ( SELECT m.profilename, s.name, @r:=case when @g=m.profilename then @r+1 else 1 end r, @g:=m.profilename FROM (select @g:=null,@r:=0) n cross join menus m left join menuitems s on m.menuid = s.menuid ) X WHERE r <= 5
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
Use the aggregate MAX(signin) grouped by id. This will list the most recent signin for each id. SELECT id, MAX(signin) AS most_recent_signin FROM tbl GROUP BY id To get the whole single record, perform an INNER JOIN against a subquery which returns only the MAX(signin) per id. SELECT tbl.id, signin, signout FROM tbl INNER JOIN … Read more