CODEWITHSUNDEEP
phpmysqlsql
l2aelba
l2aelba

Reputation: 22167

Find lowest date (custom) in mysql

I have no idea how can I find the lowest string in date-type

I will just find lowest month(by years in a table)

TableName

Id  | M  | D  |   Y   |
=======================
1  | 01  | 22  | 2012 |
2  | 11  | 29  | 2012 |
3  | 12  | 30  | 2013 |
4  | 01  | 30  | 2011 | <--- this !
5  | 12  | 14  | 2012 |

PHP:

$sql = "SELECT * FROM TableName WHERE M=?? AND Y=??";
$selected = mysql_query($sql);

so $selected should give me a result like "4/01/30/2011" (Id,M,D,Y)

Any?

Upvotes: 13

Views: 2618

Answers (8)

Arth
Arth

Reputation: 13110

bugwheels94's answer will give you the correct result:

SELECT min(concat(Y,M,D)) 
  FROM `TableName`

but this will be unable to use any index you have on any of the date's constituent fields, so will have to visit every row in the table to determine the minimum value.

Combining m4t1t0 and koopajah's answers gives you:

  SELECT * 
    FROM `TableName`
ORDER BY Y, M, D 
   LIMIT 1

This will be able to use an available index on Y, and maybe even a combined index on (Y,M,D) which can perform much faster on larger tables.

All this being said; It's almost criminal to put an answer to this question that doesn't suggest using a date field instead of your three column setup. The only reason I can think of to separate a date column would be for performance on niche queries that require separate indexes on day or month, but the choice of accepted answer suggests to me that this isn't the case.

As pointed out by Lucius.. If it's a date, store it as a date and run:

SELECT MIN(`DateColumnName`) FROM `TableName`

As a bonus this will give you access to all the MySQL Temporal functions on the column, including the ability to extract day and month, format it how you like and a single field to index and order by.

Upvotes: 10

bugwheels94
bugwheels94

Reputation: 31920

SELECT min(concat(Y,M,D)) FROM TableName

Edit: This just looks nice and clean but it is kind of very bad answer, so please use this answer

Upvotes: 18

Hafenkranich
Hafenkranich

Reputation: 1744

SELECT concat(Y,M,D) FROM TableName
ORDER BY Y ASC, M ASC, D ASC
Limit 1

That way you can return the whole row by replacing "concat(Y,M,D)" with * and easily adapt for different use cases. You could e.g. return the last date row inside the first year by:

SELECT * FROM TableName
ORDER BY Y ASC, M DESC, D DESC
Limit 1

Storing as DATETIME and using native MySQL sorting speeds it up a lot. If you need to keep the seperate values (for whatever reason, e.g. import/export with other systems out of your scope), maybe you can just add another value to the table and synchronise the values?

Upvotes: 2

Jay Doshi
Jay Doshi

Reputation: 740

I am giving you some idea for your query. Kindly follow that :

$sql="SELECT * FROM tbl_name ORDER BY Y, M, D ASC limit 1;
$res=mysql_query($sql);
$row=mysql_fetch_array($res);

$date=$row["id"]."/".$row["M"]."/".$row["D"]."/".$row["Y"];
echo $date;

I hope It will help you

Upvotes: 2

Lucius
Lucius

Reputation: 963

Please, do yourself a favour and use a date field instead.. you'll save yourself a lot of troubles.

ALTER TABLE `TableName` ADD `date` DATE NOT NULL;
UPDATE `TableName` SET `date` = CONCAT( `Y` , '-', `M` , '-', `D` );

then you'll be able to do:

SELECT MIN(`date`) FROM `TableName`

Upvotes: 9

m4t1t0
m4t1t0

Reputation: 5731

Simply perform a query ordering by year, month and day and limit your result to the first row.

SELECT * FROM TableName ORDER BY Y, M, D ASC limit 1;

Upvotes: 5

koopajah
koopajah

Reputation: 25552

Just use the ORDER BY clauses:

SELECT * FROM TableName
ORDER BY Y ASC, M ASC, D ASC

More info here : http://www.tizag.com/mysqlTutorial/mysqlorderby.php

Upvotes: 11

KaeL
KaeL

Reputation: 3659

Try this:

SELECT MIN(ColumnName) FROM TableName;

In your case, that would be:

SELECT MIN(Y) FROM TableName;

Upvotes: 4

Related Questions