469,934 Members | 2,465 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

getting max date value

121 100+
hello

Let me explain first the table structure.

t
Expand|Select|Wrap|Line Numbers
  1. able - > pds
  2.  
  3. fields - >District_ID, W_ID, Commodity_ID, Distribution_Cat_ID,  Date,  OB, Closing_Stock
  4.  
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

Expand|Select|Wrap|Line Numbers
  1. SELECT W_ID, date, Closing_Stock
  2. FROM `pds`
  3. WHERE District_ID = '3'
  4. AND Commodity_ID = '1'
  5. AND Distribution_Cat_ID = '1'
  6. AND Month( date ) = '12'
Expand|Select|Wrap|Line Numbers
  1.               W_ID  date               Closing_Stock
  2.                 176       2008-12-01      0.00
  3.         176      2008-12-10     337.36
  4.          176      2008-12-11     26.84
  5.         177      2008-12-02     0.00
  6.          177      2008-12-10     468.84
  7.          177      2008-12-11     446.52
  8.          180      2008-12-01     0.00
  9.          181     2008-12-02     0.00
  10.          181     2008-12-06     0.00
  11.          353     2008-12-02     900.00
  12.          353     2008-12-11     232.72
  13.          354     2008-12-01     5.33
  14.          354     2008-12-04     589.30
  15.          354     2008-12-10     722.45
  16.         354     2008-12-11     117.69
  17.          355     2008-12-01     1000.00
  18.          355     2008-12-06     1547.32
  19.          356     2008-12-01     1400.00
  20.          357     2008-12-02     0.00
  21.          357     2008-12-06     800.00
  22.          358     2008-12-01     600.00
  23.          358     2008-12-10     222.37
  24.          358     2008-12-11     118.20
From the above result i need like this
Expand|Select|Wrap|Line Numbers
  1. W_ID  MAX(date) Closing_Stock
  2. 176     2008-12-11        26.84
  3. 177     2008-12-11        446.52
  4. 180     2008-12-01        0.00
  5. 181     2008-12-02        0.00
  6. 353     2008-12-11        232.72
  7. 354     2008-12-11        117.69
  8. 355     2008-12-06        1547.32
  9. 356     2008-12-01         1400
  10. 357     2008-12-06         800
  11. 358     2008-12-11         118.20
anybody knows solution for the above issue please let me know.

thanks
Dec 15 '08 #1
5 3445
r035198x
13,262 8TB
Just do a where date = (select max(date) from tableName)
Dec 15 '08 #2
sbettadpur
121 100+
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
Dec 15 '08 #3
r035198x
13,262 8TB
How is the Closing_Stock showing then? Also post your current query.
Dec 15 '08 #4
sbettadpur
121 100+
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
Dec 16 '08 #5
r035198x
13,262 8TB
You In clause should just be
Expand|Select|Wrap|Line Numbers
  1. IN (SELECT MAX( Date ) FROM pds)
P.S For numeric columns you don't wrap the values in single quotes
Dec 17 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by deko | last post: by
5 posts views Thread by Nathan Sokalski | last post: by
8 posts views Thread by chimambo | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.