问题描述:

I have a table named conductor. I want to select latest records that date less than my_value.

+----+-----------+------+

| id | program | date |

+----+-----------+------+

| 1 | program 1 | 1 |

| 2 | program 1 | 3 |

| 3 | program 2 | 3 |

| 4 | program 1 | 5 |

| 5 | program 1 | 7 |

+----+-----------+------+

If we consider my_value is 4 then output will be:

+----+-----------+------+

| id | program | date |

+----+-----------+------+

| 2 | program 1 | 3 |

| 3 | program 2 | 3 |

+----+-----------+------+

How can I select records by SQL?

网友答案:
SELECT * FROM Conductor
WHERE `date` = (SELECT max(`date`) FROM Conductor
                WHERE `date` < myvalue )
网友答案:

You can try a query like:

SELECT * FROM conductor
WHERE date = (SELECT date FROM conductor 
              WHERE date < my_value ORDER BY DESC limit 1);

This should give you what you are expecting!

网友答案:
SELECT * FROM Conductor
WHERE date IN (SELECT max(date) FROM Conductor
                WHERE date < myvalue )
网友答案:
 DROP TABLE IF EXISTS my_table;

 CREATE TABLE my_table
 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
 ,program   VARCHAR(12) NOT NULL
 ,date INT NOT NULL
 );

 INSERT INTO my_table VALUES
 (1  ,'program 1',1),
 (2  ,'program 1',3),
 (3  ,'program 2',3),
 (4  ,'program 1',5),
 (5  ,'program 1',7);

 SELECT * FROM my_table;
 +----+-----------+------+
 | id | program   | date |
 +----+-----------+------+
 |  1 | program 1 |    1 |
 |  2 | program 1 |    3 |
 |  3 | program 2 |    3 |
 |  4 | program 1 |    5 |
 |  5 | program 1 |    7 |
 +----+-----------+------+

 SELECT x.* 
   FROM my_table x 
   JOIN 
      ( SELECT program
             , MAX(date) max_date 
          FROM my_table 
         WHERE date < 4 
         GROUP 
            BY program
      ) y 
     ON y.program = x.program 
    AND y.max_date = x.date;
 +----+-----------+------+
 | id | program   | date |
 +----+-----------+------+
 |  2 | program 1 |    3 |
 |  3 | program 2 |    3 |
 +----+-----------+------+
相关阅读:
Top