mysql query from log table

Question

I have a table like this

CREATE TABLE `filelog` (
  `date_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `file` varchar(200) DEFAULT NULL,
  `action` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1

action could have values 'upload' and 'delete'

I have a list view where the files that are uploaded are displayed for deletion. The problem is that the same file could be deleted and again uploaded numerous times and this is a log table where all these action are inserted. So the query should display the files with action 'upload' where the most recent action 'delete' took place before the 'upload' action.

A question with no question-mark! why don't use order by date_time desc ? — Nishant
– Nishant, Commented Mar 18, 2011 at 6:42

jon_darkstar · Accepted Answer · 2011-03-18 07:00:42Z

1

Sounds like your requirement is logically equivalent to

"Show the most recent entry for each given file IFF this most recent entry is an upload"

Is that correct? If so...

SELECT F1.*
FROM filelog F1
WHERE F1.action = 'upload' AND F1.date_time =
          SELECT max(F2.date_time)
          FROM filelog F2
          WHERE F2.file = F1.file

You still want it if there has NEVER been a deletion right?

edited Mar 18, 2011 at 7:00

answered Mar 18, 2011 at 6:46

jon_darkstar

16.9k7 gold badges35 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Pentium10 Over a year ago

If the user has re-uploaded that file twice or more, it will miss a delete action. Questionable to business logic.

jon_darkstar Over a year ago

can you give a counterexample where these are not equivalent? i dont understand what the issue is

Pentium10 Over a year ago

The issue could be when the user has 10 update actions in row, and no delete action for these. Your query will list these too.

Pentium10 Over a year ago

Yes, I am talking about that. As said it's Questionable to business logic

jon_darkstar Over a year ago

you think we only want to list the most recent upload if it immediately follows a deletion?

|

Pentium10 · Accepted Answer · 2011-03-18 06:53:21Z

0

Try this one:

SELECT * 
FROM   filelog f 
       JOIN (SELECT FILE, 
                    max(date_time) 
             FROM   filelog 
             WHERE  ACTION = 'delete' 
             GROUP  BY FILE) d 
         ON d.FILE = f.FILE 
            AND d.date_time < f.date_time 
WHERE  ACTION = 'update'

This reads the most recent deletes on files in the derived table. And joins the outer table with these.

you need indexes on

(file,date_time)

edited Mar 18, 2011 at 6:53

answered Mar 18, 2011 at 6:44

Pentium10

209k124 gold badges435 silver badges513 bronze badges

1 Comment

Tom Micheline Over a year ago

Rather than using the order by, you probably want to use max(date_time) as date_time in the select.

Collectives™ on Stack Overflow

mysql query from log table

2 Answers 2

11 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

11 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related