473,809 Members | 2,736 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

getting max date value

121 New Member
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_Ca t_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 3610
r035198x
13,262 MVP
Just do a where date = (select max(date) from tableName)
Dec 15 '08 #2
sbettadpur
121 New Member
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 MVP
How is the Closing_Stock showing then? Also post your current query.
Dec 15 '08 #4
sbettadpur
121 New Member
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
Dec 16 '08 #5
r035198x
13,262 MVP
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

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

Similar topics

11
12487
by: David Messner | last post by:
Ok I know this is simple but the statement eludes me... I have a date field where I want the default value on the data entry form's date field to be the last date entered. I figure I can do this with a query but don't know what the criteria needs to be. Any help would be appreciated, Thanks
9
2048
by: deko | last post by:
What is it with these dates? What are they so much trouble? All I want to do is subtract an hour, but not if that makes dtmTest less than dtmClt. Dim dtmStart As Date Dim dtmEnd As Date Dim dtmCtl As Date Dim dtmTest As Date Me!cbxEndTime.Requery Me!cbxStartTime.Requery
2
8091
by: Martin Raychev | last post by:
Hi all, I have the following problem: I have a private method that returns a SqlDataReader. For this to work I have not to close the DB connection in the above method. I do this only to
5
5995
by: Nathan Sokalski | last post by:
I have a user control that contains three variables which are accessed through public properties. They are declared immediately below the "Web Form Designer Generated Code" section. Every time an event is fired by one of the controls contained in the User Control, these variable are reset. Here is my current code (I have a little more to add later, right now I am just concerned about the variables getting reset): Public Class DatePicker2...
0
1250
by: salo | last post by:
Hi ........Im working with c# and asp.net and i have two calendar control in my form and i want the cliked date value to be get displayed in the textbox. i gave the code as TextBox1.Text = Calendar1.SelectedDates.ToString(); in page load event but while running nothing is displayed. and also after getting the date in the textbox ...i have to subtract the from date and to date plz can anybody help me....
1
3203
by: simbarashe | last post by:
Hie could someone please help me with getting and using the current page url. I have a function that gets the url, I want to use it with header(location : XXX) but it wont work. The code is as follows: The code below is for the first page:session_start is in line 3 <link href="css/jobSheet.css" rel="stylesheet" type="text/css" /> session_start();
8
1997
by: chimambo | last post by:
Hi Everyone, I have a huge problem. I am trying to get two corresponding variables from a Checkbox and a Text box. e.g. If I check a value, say: enrolled? (checkbox) then I have to put the date enrolled as a text. So when inserting in a table I want the enrolled to correspond to each date. Here is what I am trying but not working (In the echos is where I am trying to have the values into the table): while (list($key, $val) = @each...
9
2631
Catalyst159
by: Catalyst159 | last post by:
I have a form which is used to calculate residential Floor Area Ratio (FAR). The form is structured into seven parts as follows: Part A: Maximum FAR and Floor Area: Part B: Gross Floor Area of the main floors of the main house: Part C: Gross Floor Area of the basement or cellar: Part D: Gross Floor Area of the attic:
9
3544
vikas251074
by: vikas251074 | last post by:
I am not getting date value in spite of my good effort. This code was working in my last office where I work. Now I am trying to work at my home pc. but not getting date value. Any can help me why this happens. This is my part of code given below. <%@ Language=VBScript%> <%Option Explicit%> <html> <head> <title>SABF</title> <!--#include file="font.css"-->
7
4448
vikas251074
by: vikas251074 | last post by:
I am getting error above in following code since few days giving tension day and night. How can I solve this? I am facing since Oct.25. in line no. 362 After doing a lot of homework, I am surrendered to you. <%@ Language=VBScript%> <%Option Explicit%>
0
9721
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9601
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10635
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10378
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10115
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7653
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6881
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5687
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
3861
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.