sql statement question

ShaqDiesel · 10-16-2009, 01:56 AM

I have a table that looks like this:

Code:

comment_id(int)|timestamp(timestamp)|comment(varchar)

I want to extract the latest comment whose comment column matches 'Include:%/%'

so if I have:

Code:

1  	2009-10-15 23:21:18  	Include:\ndir1/dir2/file1.java
2 	2009-10-15 23:18:21 	blah
3 	2009-10-15 23:22:15 	Include:\ndir1/dir2/file2.java
4 	2009-10-15 23:18:53 	dir1/dir2/dir3/file3.java
5 	2009-10-15 23:19:24 	blah blah

the query should return comment 3. I tried SELECT comment FROM comments WHERE timestamp=MAX(timestamp) GROUP BY 'Include:%/%' and it didn't work.

guard · 10-16-2009, 04:47 AM

SELECT * FROM comments WHERE comment LIKE "Include:%" ORDER BY timestamp DESC LIMIT 1

bgeddy · 10-16-2009, 07:40 AM

Another way which is similar to what you where trying to do - this works so long as timestamp is unique. It may go wrong if it's not :

Code:

SELECT * from comments where timestamp=(select max(timestamp) from comments where comment like "Include:%%");

Just another idea..although guard's solution was possibly cleaner.

guard · 10-17-2009, 01:26 AM

Your idea is right and closer to the initial attempt, but I think the order and limit combination is recommended (I believe it would be better optimized by the DB engine).
Also note that your approach might not work if the timestamp is replaced with something like smalldatetime.

jlinkels · 10-17-2009, 07:52 AM

The requirement was to select the latest record which contains a certain string
If the time stamp is the wrong format, not allowing this selection, or the time stamp is not unique the database design is flawed. You cannot omit storing certain data and expect to be able to retrieve it.

Guard's first solution seems simple and correct.

jlinkels