getting max date value

[sbettadpur]

hello

Let me explain first the table structure.

t

able - > pds
 
fields - >District_ID, W_ID, Commodity_ID, Distribution_Cat_ID,  Date,  OB, Closing_Stock
 

here i want the Closing_Stock value of particular District_ID and Commodity_ID and Distribution_Cat_ID and for particular month where Date=MAX(date)

If i gave query like this

SELECT W_ID, date, Closing_Stock
FROM `pds`
WHERE District_ID = '3'
AND Commodity_ID = '1'
AND Distribution_Cat_ID = '1'
AND Month( date ) = '12'

              W_ID  date               Closing_Stock
                176       2008-12-01      0.00
        176      2008-12-10     337.36
         176      2008-12-11     26.84
        177      2008-12-02     0.00
         177      2008-12-10     468.84
         177      2008-12-11     446.52
         180      2008-12-01     0.00
         181     2008-12-02     0.00
         181     2008-12-06     0.00
         353     2008-12-02     900.00
         353     2008-12-11     232.72
         354     2008-12-01     5.33
         354     2008-12-04     589.30
         354     2008-12-10     722.45
        354     2008-12-11     117.69
         355     2008-12-01     1000.00
         355     2008-12-06     1547.32
         356     2008-12-01     1400.00
         357     2008-12-02     0.00
         357     2008-12-06     800.00
         358     2008-12-01     600.00
         358     2008-12-10     222.37
         358     2008-12-11     118.20

From the above result i need like this

W_ID  MAX(date) Closing_Stock
176     2008-12-11        26.84
177     2008-12-11        446.52
180     2008-12-01        0.00
181     2008-12-02        0.00
353     2008-12-11        232.72
354     2008-12-11        117.69
355     2008-12-06        1547.32
356     2008-12-01         1400
357     2008-12-06         800
358     2008-12-11         118.20

anybody knows solution for the above issue please let me know.

thanks

[r035198x]

Just do a where date = (select max(date) from tableName)

[sbettadpur]

Thanks for your reply,

I tried this logic

date = (select max(date) from tableName)

but i need max(date) all W_ID(wholesale point)

so i tried like this date in (select max(date) from tablename)
from above query i getting proper MAX date of all W_ID but the value of Closing_Stock is showing not proper

[r035198x]

How is the Closing_Stock showing then? Also post your current query.

[sbettadpur]

SELECT W_ID, Date, Closing_Stock
FROM pds
WHERE District_ID = '3'
AND Commodity_ID = '1'
AND Distribution_Cat_ID = '1'
AND Month( date ) = '12'
AND Date
IN (

SELECT MAX( Date )
FROM pds
WHERE District_ID = '3'
AND Commodity_ID = '1'
AND Distribution_Cat_ID = '1'
AND Month( date ) = '12'
GROUP BY W_ID
)
GROUP BY W_ID

[r035198x]

You In clause should just be

IN (SELECT MAX( Date ) FROM pds)

P.S For numeric columns you don't wrap the values in single quotes