Menu
Daft SQL question. I have a table like so ('pid' is auto-increment primary col)
CREATE TABLE theTable ( `pid` INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, `timestamp` TIMESTAMP DEFAULT CURRENT_TIMESTAMP, `cost` INT UNSIGNED NOT NULL, `rid` INT NOT NULL, ) Engine=InnoDB; Actual table data:
INSERT INTO theTable (`pid`, `timestamp`, `cost`, `rid`) VALUES (1, '2011-04-14 01:05:07', 1122, 1), (2, '2011-04-14 00:05:07', 2233, 1), (3, '2011-04-14 01:05:41', 4455, 2), (4, '2011-04-14 01:01:11', 5566, 2), (5, '2011-04-14 01:06:06', 345, 1), (6, '2011-04-13 22:06:06', 543, 2), (7, '2011-04-14 01:14:14', 5435, 3), (8, '2011-04-14 01:10:13', 6767, 3) ; I want to get the PID of the latest row for each rid (1 result per unique RID). For the sample data, I'd like:
pid | MAX(timestamp) | rid ----------------------------------- 5 | 2011-04-14 01:06:06 | 1 3 | 2011-04-14 01:05:41 | 2 7 | 2011-04-14 01:14:14 | 3 I've tried running the following query:
SELECT MAX(timestamp),rid,pid FROM theTable GROUP BY rid and I get:
max(timestamp) ; rid; pid ---------------------------- 2011-04-14 01:06:06; 1 ; 1 2011-04-14 01:05:41; 2 ; 3 2011-04-14 01:14:14; 3 ; 7 The PID returned is always the first occurence of PID for an RID (row / pid 1 is frst time rid 1 is used, row / pid 3 the first time RID 2 is used, row / pid 7 is first time rid 3 is used). Though returning the max timestamp for each rid, the pids are not the pids for the timestamps from the original table. What query would give me the results I'm looking for?
577
Example - Using SQL GROUP BY Clause SELECT department, MAX(salary) AS "Highest salary" FROM employees GROUP BY department; Because you have listed one column in your SQL SELECT statement that is not encapsulated in the MAX function, you must use the SQL GROUP BY clause.
The MySQL MAX() function is used to return the maximum value in a set of values of an expression. This aggregate function is useful when we need to find the maximum number, selecting the most expensive product, or getting the largest payment to the customer from your table.
The MySQL Solution If you're working with MySQL, you can combine MAX() with the GREATEST() function to get the biggest value from two or more fields. Here's the syntax for GREATEST: GREATEST(value1,value2,...)
You are grouping values there (see Group By in the end) and so your max function calculates Max value per group. If you wand an absolute max value, remove the grouping. See the top answer, it gives you a correct query, just replace LIMIT with SELECT TOP 1 as I mentioned there, this would be the syntax for sql server.
(Tested in PostgreSQL 9.something)
Identify the rid and timestamp.
select rid, max(timestamp) as ts from test group by rid; 1 2011-04-14 18:46:00 2 2011-04-14 14:59:00 Join to it.
select test.pid, test.cost, test.timestamp, test.rid from test inner join (select rid, max(timestamp) as ts from test group by rid) maxt on (test.rid = maxt.rid and test.timestamp = maxt.ts) 
select * from ( select `pid`, `timestamp`, `cost`, `rid` from theTable order by `timestamp` desc ) as mynewtable group by mynewtable.`rid` order by mynewtable.`timestamp` Hope I helped !
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
