Group by Max

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi:

I have a quey that runs between two dates; on 1/13/2005 (is the last day)
I have a record; when I group the date by Max to get just the records from
1/13/2005 I do not get the record?

Any answer is appreciated

Thanks,

Dan
 
D said:
Hi:

I have a quey that runs between two dates; on 1/13/2005 (is the last day)
I have a record; when I group the date by Max to get just the records from
1/13/2005 I do not get the record?
Click to expand...

It's DATE/TIME so don't forget that MAX will also use the time on these
fields.
 
HI:

I am running the query today with NO Max and I get the record from
1/13/2005; if I group by Max I do not get that record???

Thanks,

Dan
 
I just created a table with date/time and ran a "SELECT MAX(Submitted) FROM
table;" and it returned the latest date.

Post your query/table structure.
 
Hi:

Query with the 1/13/2005, no max:

SELECT ODSDBA_WATS5MIX.NATL_STR_NBR, ODSDBA_WATS5MIX.MENU_ITM_NBR,
ODSDBA_WATS5MIX.DLY_SLS_DT, ODSDBA_WATP1MIT.MENU_ITEM_CD,
ODSDBA_WATP1MIT.AMIL_FG, ODSDBA_WATP1MIT.MENU_ITEM_STS
FROM ODSDBA_WATS5MIX INNER JOIN ODSDBA_WATP1MIT ON
ODSDBA_WATS5MIX.MENU_ITM_NBR = ODSDBA_WATP1MIT.MENU_ITEM_CD
WHERE (((ODSDBA_WATS5MIX.NATL_STR_NBR)=40111) AND
((ODSDBA_WATS5MIX.MENU_ITM_NBR) In (859)) AND ((ODSDBA_WATS5MIX.DLY_SLS_DT)
Between [Please enter the Start Date] And [Please enter the End Date]));
****

Qry with Max no 1/13/2005:

SELECT ODSDBA_WATS5MIX.NATL_STR_NBR, ODSDBA_WATS5MIX.MENU_ITM_NBR,
Max(ODSDBA_WATS5MIX.DLY_SLS_DT) AS MaxOfDLY_SLS_DT,
ODSDBA_WATP1MIT.MENU_ITEM_CD, ODSDBA_WATP1MIT.AMIL_FG,
ODSDBA_WATP1MIT.MENU_ITEM_STS
FROM ODSDBA_WATS5MIX INNER JOIN ODSDBA_WATP1MIT ON
ODSDBA_WATS5MIX.MENU_ITM_NBR = ODSDBA_WATP1MIT.MENU_ITEM_CD
GROUP BY ODSDBA_WATS5MIX.NATL_STR_NBR, ODSDBA_WATS5MIX.MENU_ITM_NBR,
ODSDBA_WATP1MIT.MENU_ITEM_CD, ODSDBA_WATP1MIT.AMIL_FG,
ODSDBA_WATP1MIT.MENU_ITEM_STS
HAVING (((ODSDBA_WATS5MIX.NATL_STR_NBR)=40111) AND
((ODSDBA_WATS5MIX.MENU_ITM_NBR) In (859)) AND
((Max(ODSDBA_WATS5MIX.DLY_SLS_DT)) Between [Please enter the Start Date] And
[Please enter the End Date]));
*****

Thanks,

Dan
 
Hi again:

I was able to solve the problem.

You have to create a sub-query with Max based on the query with no Max -
same fields.

Thanks everyone,

Dan

D said:
Hi:

Query with the 1/13/2005, no max:

SELECT ODSDBA_WATS5MIX.NATL_STR_NBR, ODSDBA_WATS5MIX.MENU_ITM_NBR,
ODSDBA_WATS5MIX.DLY_SLS_DT, ODSDBA_WATP1MIT.MENU_ITEM_CD,
ODSDBA_WATP1MIT.AMIL_FG, ODSDBA_WATP1MIT.MENU_ITEM_STS
FROM ODSDBA_WATS5MIX INNER JOIN ODSDBA_WATP1MIT ON
ODSDBA_WATS5MIX.MENU_ITM_NBR = ODSDBA_WATP1MIT.MENU_ITEM_CD
WHERE (((ODSDBA_WATS5MIX.NATL_STR_NBR)=40111) AND
((ODSDBA_WATS5MIX.MENU_ITM_NBR) In (859)) AND ((ODSDBA_WATS5MIX.DLY_SLS_DT)
Between [Please enter the Start Date] And [Please enter the End Date]));
****

Qry with Max no 1/13/2005:

SELECT ODSDBA_WATS5MIX.NATL_STR_NBR, ODSDBA_WATS5MIX.MENU_ITM_NBR,
Max(ODSDBA_WATS5MIX.DLY_SLS_DT) AS MaxOfDLY_SLS_DT,
ODSDBA_WATP1MIT.MENU_ITEM_CD, ODSDBA_WATP1MIT.AMIL_FG,
ODSDBA_WATP1MIT.MENU_ITEM_STS
FROM ODSDBA_WATS5MIX INNER JOIN ODSDBA_WATP1MIT ON
ODSDBA_WATS5MIX.MENU_ITM_NBR = ODSDBA_WATP1MIT.MENU_ITEM_CD
GROUP BY ODSDBA_WATS5MIX.NATL_STR_NBR, ODSDBA_WATS5MIX.MENU_ITM_NBR,
ODSDBA_WATP1MIT.MENU_ITEM_CD, ODSDBA_WATP1MIT.AMIL_FG,
ODSDBA_WATP1MIT.MENU_ITEM_STS
HAVING (((ODSDBA_WATS5MIX.NATL_STR_NBR)=40111) AND
((ODSDBA_WATS5MIX.MENU_ITM_NBR) In (859)) AND
((Max(ODSDBA_WATS5MIX.DLY_SLS_DT)) Between [Please enter the Start Date] And
[Please enter the End Date]));
*****

Thanks,

Dan


Noozer said:
I just created a table with date/time and ran a "SELECT MAX(Submitted) FROM
table;" and it returned the latest date.

Post your query/table structure.
Click to expand...
Click to expand...
 
Back
Top