Weird MySql query

[rohitbanerjee]

So suppose I have a mysql table like the following

 

          Col1  Col2  Col3

Row1: A      10    2012-3-21 13:20:09

Row2: A      12    2012-3-20 12:04:09

Row3: A      5      2012-3-20 12:03:03

 

I need a query which will fetch me Row1 and Row2 given that I am searching for A, if the query sees that two rows occur on the same date, it should return the latest one. Any thoughts?

[silkfire]

This depends on a few things. Do you always search for 2 rows at a time? Is it possible to separate date and time?

 

I could think of using ORDER BY and LIMIT to achieve what you require.

[MMDE]

I believe you must use an INNER JOIN. I'm not very good at it, so I'm not going to give you an example, sorry.

[PFMaBiSmAd]

To get specific rows within a group, see this link - http://dev.mysql.com/doc/refman/5.5/en/example-maximum-column-group-row.html

[Muddy_Funster]

Pretty sure this will error out all over the place, but I would try something like:

SELECT Col1, Col2, Col3
FROM table1
WHERE(
Col1 = 'A'
AND
DATE(Col3) IN 
(
SELECT DATE(Col3) as date_range FROM table 1 AS dt_table1 WHERE 
  (
  TIME(td_table1.Col3) IN 
    (
    SELECT MAX(TIME(Col3)) as max_time FROM table1 as tt_table1 GROUP BY DATE(tt_table1.Col3)
    )
   )
  )
)
)

.....actualy, no I woudln't, I'd already have a date column in the table that I could use for this lookup.

[rohitbanerjee]

Thanks for all the input -- turns out DISTINCT(col3) works pretty well.