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_Ca t_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_Ca t_ID = '1'
AND Month( date ) = '12'
AND Date
IN (

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

[r035198x]

You In clause should just be [code=mysql]IN (SELECT MAX( Date ) FROM pds)[/code]
P.S For numeric columns you don't wrap the values in single quotes