MAX_value_of_a_column!!!_¿what's_happening?

External Since: Feb 08, 2008 Posts: 17

Hi, i have a rawdata like this:

PMM_DATETIME, Load_Begin
02/11/2008 12:00:00.320 AM 5
02/11/2008 12:00:05.150 AM 6
02/11/2008 12:00:05.383 AM 7
02/11/2008 12:00:06.803 AM 8
02/11/2008 12:00:10.130 AM 5
02/11/2008 12:00:13.216 AM 5
02/11/2008 12:00:13.670 AM 5
02/11/2008 12:00:16.563 AM 4
02/11/2008 12:00:18.670 AM 4
02/11/2008 12:00:28.100 AM 3
..
..
..
In fact, there are over 6 million of row like this so, i want the max
value of the column Load_Begin given a specific day for example:

SELECT
CONVERT(VARCHAR(10),PMM_DATETIME,102) + ' ' +
CONVERT(VARCHAR( Cool

,PMM_DATETIME,108) AS PMM_DATETIME,
MAX(CONVERT(INT,Load_Begin)) AS MAX_VALUE
FROM SES
WHERE PMM_DATETIME >= '2008-11-02 00:00:00' and PMM_DATETIME <
'2008-11-03'

then if i just do SELECT MAX(CONVERT(INT,Load_Begin)) AS MAX_VALUE it
returns the max value and its ok, but i want to know the exactly time
so i put the CONVERT(VARCHAR(10),PMM_DATETIME,102) + ' ' +
CONVERT(VARCHAR( Cool

,PMM_DATETIME,108) AS PMM_DATETIME, and group all
the results but it returns many rows i am doing this:

SELECT
CONVERT(VARCHAR(10),PMM_DATETIME,102) + ' ' +
CONVERT(VARCHAR( Cool

,PMM_DATETIME,108) AS PMM_DATETIME,
MAX(CONVERT(INT,Load_Begin)) AS MAX_VALUE
FROM SES
WHERE PMM_DATETIME >= '2008-11-02 00:00:00' and PMM_DATETIME <
'2008-11-03'
AND PDE_Type = 0
GROUP BY CONVERT(VARCHAR(10),PMM_DATETIME,102) + ' ' +
CONVERT(VARCHAR( Cool

,PMM_DATETIME,108)

i try without the convert clause and its the same problem, please help
me!!!

Regards

External Since: Jul 14, 2008 Posts: 10

On Nov 3, 12:05 pm, Jorge Reyes wrote:
> Hi, i have a rawdata like this:
>
> PMM_DATETIME, Load_Begin
> 02/11/2008 12:00:00.320 AM 5
> 02/11/2008 12:00:05.150 AM 6
> 02/11/2008 12:00:05.383 AM 7
> 02/11/2008 12:00:06.803 AM 8
> 02/11/2008 12:00:10.130 AM 5
> 02/11/2008 12:00:13.216 AM 5
> 02/11/2008 12:00:13.670 AM 5
> 02/11/2008 12:00:16.563 AM 4
> 02/11/2008 12:00:18.670 AM 4
> 02/11/2008 12:00:28.100 AM 3
> .
> .
> .
> In fact, there are over 6 million of row like this so, i want the max
> value of the column Load_Begin given a specific day for example:
>
> SELECT
> CONVERT(VARCHAR(10),PMM_DATETIME,102) + ' ' +
> CONVERT(VARCHAR( Cool

,PMM_DATETIME,108) AS PMM_DATETIME,
> MAX(CONVERT(INT,Load_Begin)) AS MAX_VALUE
> FROM SES
> WHERE PMM_DATETIME >= '2008-11-02 00:00:00' and PMM_DATETIME <
> '2008-11-03'
>
> then if i just do SELECT MAX(CONVERT(INT,Load_Begin)) AS MAX_VALUE it
> returns the max value and its ok, but i want to know the exactly time
> so i put the CONVERT(VARCHAR(10),PMM_DATETIME,102) + ' ' +
> CONVERT(VARCHAR( Cool

,PMM_DATETIME,108) AS PMM_DATETIME, and group all
> the results but it returns many rows i am doing this:
>
> SELECT
> CONVERT(VARCHAR(10),PMM_DATETIME,102) + ' ' +
> CONVERT(VARCHAR( Cool

,PMM_DATETIME,108) AS PMM_DATETIME,
> MAX(CONVERT(INT,Load_Begin)) AS MAX_VALUE
> FROM SES
> WHERE PMM_DATETIME >= '2008-11-02 00:00:00' and PMM_DATETIME <
> '2008-11-03'
> AND PDE_Type = 0
> GROUP BY CONVERT(VARCHAR(10),PMM_DATETIME,102) + ' ' +
> CONVERT(VARCHAR( Cool

,PMM_DATETIME,108)
>
> i try without the convert clause and its the same problem, please help
> me!!!
>
> Regards

First off, think about what you are grouping by. Your problem
description says
that you want the maximum value of Load_Begin for a given *day*.
In your code you are grouping by the *date and time* - so you are
getting far
more groups than you want.

It also appears that your raw data can have multiple rows with the
same Load_Begin
value on the same day, just at different times. It is unclear if this
can be true for the
maximum value, but I'll assume that it is possible, and so it is
unclear if you want to know all of the times that the max value
occurred at,
or just one, and if just one which one...

I modified your raw data slightly for clarity:
a) to make the rows distinct by minute rather than seconds
so that the default datetime display format would show them as
distinct values.
b) modified one of the rows so there would be 2 rows on 2/11 that had
the maximum value
of 8 for Load_Begin
c) added 2 rows with data from other dates and Load_Begin values of 9

drop table x
go
create table x ( PMM_DATETIME datetime, Load_Begin int)
go

insert x values ("02/10/2008 12:02:22:022 AM", 9)
insert x values ("02/11/2008 12:01:00.320 AM", 5)
insert x values ("02/11/2008 12:02:05.150 AM", 6)
insert x values ("02/11/2008 12:03:05.383 AM", 7)
insert x values ("02/11/2008 12:04:06.803 AM", Cool

insert x values ("02/11/2008 12:05:10.130 AM", 5)
insert x values ("02/11/2008 12:06:13.216 AM", 5)
insert x values ("02/11/2008 12:07:13.670 AM", 5)
insert x values ("02/11/2008 12:08:16.563 AM", 4)
insert x values ("02/11/2008 12:09:18.670 AM", Cool

insert x values ("02/11/2008 12:10:28.100 AM", 3)
insert x values ("02/12/2008 12:08:15.444 AM", 9)
go

select PMM_DATETIME, max(Load_Begin)
from x
group by convert(date,PMM_DATETIME)
having Load_Begin = max(Load_Begin)
go

PMM_DATETIME
-------------------------- -----------
Feb 11 2008 12:04AM 8
Feb 11 2008 12:09AM 8
Feb 10 2008 12:02AM 9
Feb 12 2008 12:08AM 9

(4 rows affected)

select PMM_DATETIME, max(Load_Begin)
from x
group by convert(date,PMM_DATETIME)
having Load_Begin = max(Load_Begin)
and PMM_DATETIME between "02/11/2008" and "02/12/2008"
go

PMM_DATETIME
-------------------------- -----------
Feb 11 2008 12:04AM 8
Feb 11 2008 12:09AM 8

(2 rows affected)