I have no idea how can I find lowest string in date-type
I will just finding lowest month(by years in 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 will give result like "4/01/30/2011" (Id,M,D,Y)
Any ?
8 Answers
Answers 1
SELECT min(concat(Y,M,D)) FROM TableName Answers 2
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
Answers 3
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` Answers 4
user1533609'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.
Answers 5
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; Answers 6
Try this:
SELECT MIN(ColumnName) FROM TableName; In your case, that would be:
SELECT MIN(Y) FROM TableName; Answers 7
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
Answers 8
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?
0 comments:
Post a Comment