SQL Query wanted

[vijayamani]

Hai Everyone..
I am new to this forum & MySQL..

I hava a table like this

iss_date    item_code  aa     bb    cc
2010-06-05  abc        10     15    15
2010-06-10  cca        20     20    20
2010-06-12  abc        12     12    12
2010-06-13  abc        13     13    17
2010-06-15  cca        18     17    24

I need a query that will fetch record
for a particular date. If data for particular
date is not found, it should return previous
date's values.

for ex..
data for iss_date=2010-06-15 should return as

item_code     aa    bb    cc
cca           18    17    24
abc           13    13    17

data for iss_date=2010-06-12 should return as

item_code     aa    bb    cc
cca           20    20    20
abc           12    12    12

Thanks in advance

[hirenpatel53]

select item_code,aa,bb,cc from tablename
where iss_date=2010-06-15

[vijayamani]

thanks hirenpatel53 for the reply.

Your query will fetch all records where iss_date=2010-06-15.
the result will be

item_code     aa    bb    cc
cca           18    17    24

But I need the result as
(I need all items (under item_code)

item_code     aa    bb    cc
cca           18    17    24
abc           13    13    17

I need if any record is present for particular item_code
then the previous date's item_code details should be displayed
as above.

I think you'll understand my requirement.

[tyson.crouch]

SELECT * FROM tablename
WHERE iss_date <= 2010-06-15
ORDER BY item_code, aa, bb, cc

This query will return all records from the table where the date is LESS THAN a given date, though is inclusive of the date given.

[vijayamani]

Thanks tyson.crouch for the reply.

My problem is
1) select single item_code
2) check if data is available for the given date
3) if available show that
4) else if data is not available for given date
find the previous date for which data is available & show it

I think i could not present my problem..!?

[pritaeas]

SELECT * FROM tablename
WHERE iss_date <= 2010-06-15
ORDER BY iss_date DESC
LIMIT 1

[vijayamani]

thanks pritaeas.

That works.

[sandeepji1]

Hi, thanks pritaeas for sharing that info with all.But here I added some basic point that's makes that Query more understandable,
1)For Retriving data from database we use "Select * from table_name.
2)order by: by default, it's show result in ascending,
or ORDER BY column_name ASC,
But as we mention ORDER BY column_name DESC, result we get in descending order.
3)Limit(starting row, number of row)
eg.Limit(2,8)
means start from 2nd row and retrieve data up-to row 8.
I Thinks that's take an advantage for clearing all concept, Regrading that query.

[vijayamani]

Nice..sandeepji1

thank you.