mysql select query

Question

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?

• Kishan Gajjar: pls elaborate your question with proper example
• delete my account: @Kishan Gajjar:I have explained very clearly.What you did not understand here.

Answer 1

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:

• name of table
• fields and field types
• Do you have a way to keep correct track of timestamps and dates?

• OverLex: It's a matter of perspective: how can you show something to someone who doesn't know what to want? (philosophy :D) You can always assume a default year: I don't know, 2011 or just use max(year) as you used max(day), as I told you, it would be helpful to know your table structure and/or timekeeping fields.
• delete my account: I get o/p as four records but for this query it should only show me the max date record.I have four columns date month year and this three combined yyyy-mm-dd.
• OverLex: Try select max(threecombined) from table where month = 'Month' and year = 'year' it should select the max day where month and year are what you want. BTW: what a strange way to arrange dates...
• delete my account: I tried your answer but i get many records as o/p.I should get only one which is maximum.

Answer 2

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

Answer 3

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.

• delete my account: I have colmn for date and year separately then how will the query come
• Abhay: @user22115: Does the column for date only store date or date-month? I think it should be simple. Using the 2 columns that you have, assuming one stores date-month (e.g. 21/06) and another only year (e.g. 2011), the query could be like: SELECT *, STR_TO_DATE(CONCAT(`insert_datemonth`, '/', `insert_year`), '%d/%m/%Y') as `ins_date` WHERE ... ORDER BY `ins_date` DESC .... Hope it helps
• delete my account: Nope.date stores date,month stores month and year stores year.
• Abhay: @delete my account: please see my updated query based on your last feedback that there are 3 different columns for date, month and year respectively