CODEWITHSUNDEEP
mysqlsqlselectmax
poetter747
poetter747

Reputation: 429

How to SELECT by MAX(date)?

This is the table structure:

CREATE TABLE `reports` (
  `report_id` int(11) NOT NULL auto_increment,
  `computer_id` int(11) NOT NULL default '0',
  `date_entered` datetime NOT NULL default '1970-01-01 00:00:00',
  `total_seconds` int(11) NOT NULL default '0',
  `iphone_id` int(11) default '0',
  PRIMARY KEY  (`report_id`),
  KEY `computer_id` (`computer_id`),
  KEY `iphone_id` (`iphone_id`)
) ENGINE=MyISAM AUTO_INCREMENT=120990 DEFAULT CHARSET=latin1

I need a SELECT statement that will list the report_id per computer_id from latest entered date_entered, and I have no clue how to do that.

Upvotes: 38

Views: 327382

Answers (14)

Chimdi
Chimdi

Reputation: 377

You can make use of MySql variables as so:

select max(date_entered) into @maxdate FROM reports;
SELECT report_id, computer_id, date_entered FROM reports WHERE date_entered=@maxdate;

Upvotes: 0

Wil
Wil

Reputation: 837

If you sort the table by date_entered descending, grouping collects the correct rows:

SELECT * FROM (
  SELECT report_id, computer_id, date_entered ORDER BY date_entered DESC
) sorted 
GROUP BY computer_id

This fully pages in the indices (assuming you indexed all three columns - otherwise full table scan), but if your indices are on HDD that will often be the fastest solution.

Upvotes: 0

east
east

Reputation: 15

It works great for me

SELECT
    report_id,computer_id,MAX(date_entered)
FROM
    reports
GROUP BY
    computer_id

Upvotes: -1

siraj k
siraj k

Reputation: 19

select report_id, computer_id, date_entered
into #latest_date
from reports a
where exists(select 'x' from reports 
                where a.report_id = report_id
                group by report_id having max(date_entered) =   a.date_entered)

select * from #latest_leave where computer_id = ##

Upvotes: 1

chibucious
chibucious

Reputation: 41

This would work perfectely, if you are using current timestamp

SELECT * FROM reports WHERE date_entered = (SELECT max(date_entered) FROM REPORTS)

This would also work, if you are not using current timestamp but you are using date and time column seperately

SELECT * FROM reports WHERE date_entered = (SELECT max(date_entered) FROM REPORTS) ORDER BY time DESC LIMIT 1

Upvotes: 4

LuyRamone
LuyRamone

Reputation: 35

I use this solution having max(date_entered) and it works very well

SELECT 
  report_id, 
  computer_id, 
  date_entered
FROM reports
GROUP BY computer_id having max(date_entered)

Upvotes: -2

sixstring
sixstring

Reputation: 262

Did this on a blog engine to get the latest blog. I adapted it to your table structure.

SELECT * FROM reports WHERE date_entered = (SELECT max(date_entered) FROM REPORTS)

Upvotes: -1

xayer
xayer

Reputation: 479

Works perfect for me:

(SELECT content FROM tblopportunitycomments WHERE opportunityid = 1 ORDER BY dateadded DESC LIMIT 1);

Upvotes: 1

Gianluca Demarinis
Gianluca Demarinis

Reputation: 2191

Workaround but working solution

Only if ID is autoincrement, you can search for the maximum id instead of the max date. So, by the ID you can find all others fields.

select *
from table
where id IN ( 
              select max(id)
              from table
              group by #MY_FIELD#
              )

Upvotes: 1

Jeremy
Jeremy

Reputation: 1972

This is a very old question but I came here due to the same issue, so I am leaving this here to help any others.

I was trying to optimize the query because it was taking over 5 minutes to query the DB due to the amount of data. My query was similar to the accepted answer's query. Pablo's comment pushed me in the right direction and my 5 minute query became 0.016 seconds. So to help any others that are having very long query times try using an uncorrelated subquery.

The example for the OP would be:

SELECT 
    a.report_id, 
    a.computer_id, 
    a.date_entered
FROM reports AS a
    JOIN (
        SELECT report_id, computer_id, MAX(date_entered) as max_date_entered
        FROM reports
        GROUP BY report_id, computer_id
    ) as b
WHERE a.report_id = b.report_id
    AND a.computer_id = b.computer_id
    AND a.date_entered = b.max_date_entered

Thank you Pablo for the comment. You saved me big time!

Upvotes: 5

Daniel Gray
Daniel Gray

Reputation: 1

SELECT report_id, computer_id, date_entered
FROM reports
WHERE date_entered = (
    SELECT date_entered 
    FROM reports 
    ORDER date_entered 
    DESC LIMIT 1
)

Upvotes: -1

Frane Poljak
Frane Poljak

Reputation: 2365

Accordig to this: https://bugs.mysql.com/bug.php?id=54784 casting as char should do the trick:

SELECT report_id, computer_id, MAX(CAST(date_entered AS CHAR))
FROM reports
GROUP BY report_id, computer_id

Upvotes: 5

bhamby
bhamby

Reputation: 15440

This should do it:

SELECT report_id, computer_id, date_entered
FROM reports AS a
WHERE date_entered = (
    SELECT MAX(date_entered)
    FROM reports AS b
    WHERE a.report_id = b.report_id
      AND a.computer_id = b.computer_id
)

Upvotes: 65

Tyler Ferraro
Tyler Ferraro

Reputation: 3772

Are you only wanting it to show the last date_entered, or to order by starting with the last_date entered?

SELECT report_id, computer_id, date_entered
FROM reports
GROUP BY computer_id
ORDER BY date_entered DESC
-- LIMIT 1 -- uncomment to only show the last date.

Upvotes: 19

Related Questions