I search and get 10 records as result.I have two drop down boxes.one will have month as value and other one will have year as the value.Say those 10 records have same year and 5 have month as jan and other five has month as feb.
When user clicks feb then five ids will be passed to my query but i need to pull the younger document.2 docs were inserted on 5th of feb and other tow 10 feb and remaining one 25feb.i need to pull this 25th feb document.
how to select this using select statement?
You can extract day and time from the database and have them shown to the user so he can select the correct document, otherwise you can solve with:
SELECT *
FROM TABLE
WHERE month = 'Feb'
AND year = 2011
AND day = (select max(day) from table where month = 'Feb' and year = 2011 )
But I'm supposing a lot of information here, these infos should help me help you out:
Presumably you have some date_inserted column in your database - if so, you can add
ORDER BY date_inserted DESC LIMIT 1
This will put them in reverse date order, and LIMIT 1 will cause it to only return 1 result
This should work:
SELECT *
FROM `table`
WHERE MONTH(`insert_date`) = 2
AND YEAR(`insert_date`) = 2011
ORDER BY `insert_date` DESC
LIMIT 1;
Note: the above assumes you have a field in your table for storing the date on which the document was created/inserted. Please replace insert_date and table in the above query with the respective column name and table name.
EDITED after this comment "date stores date,month stores month and year stores year"
SELECT *
FROM `table`
WHERE `month` = 2
AND `year` = 2011
ORDER BY `year` DESC, `month` DESC, `date` DESC
LIMIT 1;
I've assumed that in the month column you are storing numbers, 1 for Jan, 2 for Feb and so on. If however you are storing the 3-letter month name, then instead of "`month` = 2" please use this:
MONTH(STR_TO_DATE(`month`, '%b')) = 2
Hope this should work.
SELECT *, STR_TO_DATE(CONCAT(`insert_datemonth`, '/', `insert_year`), '%d/%m/%Y') as `ins_date` WHERE ... ORDER BY `ins_date` DESC .... Hope it helps