get the ID value from MAX value of a set of columns?

Hi to everybody
I'm having some trouble while trying to solve this issue.
I have this table:
DAY HOUR MINUTE
ID FIX_VALUE_A FIX_VALUE_B VARIABLE_C VARIABLE_D VARIABLE_E
103 897544 750916 26 17 04
165 897544 750916 25 17 13
203 897544 750916 25 17 02
514 334692 264553 15 03 46
517 334692 264553 16 03 43
1060 334692 264553 16 10 01
I need to group the rows with same FIX_VALUES and, from each group, get
the ID of the row with maximum set of VARIABLE_VALUES
Actually,
VARIABLE_VALUE_C = DAY
VARIABLE_VALUE_D = HOUR
VARIABLE_VALUE_E = MINUTE
and the three of them come from a SUBSTRING(TIMESTAMP_FIELD,X,Y).
I need to get the ID of the row with the highest set of
(DAY,HOUR,MINUTE)

>From the previous example, the expected results should be ID=103 and
ID=1060
Could anybody help me?
TIA,
David Grantselect ID, FIX_VALUE_A, FIX_VALUE_B
from T as T1
where not exists (
select * from T as T2
where T2.TIMESTAMP_FIELD > T1.TIMESTAMP_FIELD
)
If the TIMESTAMP_FIELD values aren't ordered correctly, you
can try
...
where not exists (
select * from T as T2
where T2.VARIABLE_C + T2.VARIABLE_D + T2.VARIABLE_E
> T1.VARIABLE_C + T1.VARIABLE_D + T1.VARIABLE_E
)
Without seeing the details, I'm doing some guessing about types, but
hopefully this will be helpful.
Steve Kass
Drew University
icebold54@.hotmail.com wrote:

>Hi to everybody
>I'm having some trouble while trying to solve this issue.
>I have this table:
> DAY HOUR MINUTE
>ID FIX_VALUE_A FIX_VALUE_B VARIABLE_C VARIABLE_D VARIABLE_E
>103 897544 750916 26 17 04
>165 897544 750916 25 17 13
>203 897544 750916 25 17 02
>514 334692 264553 15 03 46
>517 334692 264553 16 03 43
>1060 334692 264553 16 10 01
>
>I need to group the rows with same FIX_VALUES and, from each group, get
>the ID of the row with maximum set of VARIABLE_VALUES
>Actually,
>VARIABLE_VALUE_C = DAY
>VARIABLE_VALUE_D = HOUR
>VARIABLE_VALUE_E = MINUTE
>and the three of them come from a SUBSTRING(TIMESTAMP_FIELD,X,Y).
>I need to get the ID of the row with the highest set of
>(DAY,HOUR,MINUTE)
>
>ID=1060
>
>Could anybody help me?
>TIA,
>David Grant
>
>|||>> I need to get the ID of the row with the highest set of (DAY,HOUR,MINUTE)
One simple approach is:
SELECT *
FROM tbl t1
WHERE CAST( t1.day AS CHAR( 10 ) +
CAST( t1.day AS CHAR( 10 ) +
CAST( t1.day AS CHAR( 10 ) =
( SELECT MAX( CAST( t1.day AS CHAR( 10 ) +
CAST( t1.day AS CHAR( 10 ) +
CAST( t1.day AS CHAR( 10 ) )
FROM tbl t2
WHERE t2.fix_col1 = t1.fix_col2
AND t2.fix_col2 = t2.fix_col2 ) ;
Have you considered representing the value in a single DATETIME column?
Anith|||Try,
select
FIX_VALUE_A,
FIX_VALUE_B,
[id]
from
t1 as a
where
((((VARIABLE_C * 100) + VARIABLE_D) * 100) + VARIABLE_E) = (select
max((((b.VARIABLE_C * 100) + b.VARIABLE_D) * 100) + b.VARIABLE_E) from t1 as
b where b.FIX_VALUE_A = a.FIX_VALUE_A and b.FIX_VALUE_B = a.FIX_VALUE_B)
go
AMB
"icebold54@.hotmail.com" wrote:

> Hi to everybody
> I'm having some trouble while trying to solve this issue.
> I have this table:
> DAY HOUR MINUTE
> ID FIX_VALUE_A FIX_VALUE_B VARIABLE_C VARIABLE_D VARIABLE_E
> 103 897544 750916 26 17 04
> 165 897544 750916 25 17 13
> 203 897544 750916 25 17 02
> 514 334692 264553 15 03 46
> 517 334692 264553 16 03 43
> 1060 334692 264553 16 10 01
>
> I need to group the rows with same FIX_VALUES and, from each group, get
> the ID of the row with maximum set of VARIABLE_VALUES
> Actually,
> VARIABLE_VALUE_C = DAY
> VARIABLE_VALUE_D = HOUR
> VARIABLE_VALUE_E = MINUTE
> and the three of them come from a SUBSTRING(TIMESTAMP_FIELD,X,Y).
> I need to get the ID of the row with the highest set of
> (DAY,HOUR,MINUTE)
>
> ID=1060
>
> Could anybody help me?
> TIA,
> David Grant
>|||Thank your for your interest, Steve.
The table has this aspect:
ID FIXA FIXB FIXC FIXD VARE VARF VARG VARH VARI VAR J
107 36902 7187815 2004 8 2004 09 23 14 06 50
104 36902 7187815 2004 8 2004 09 15 17 06 50
101 36902 7187815 2004 8 2004 09 15 14 07 50
14 36902 7187815 2004 8 2004 09 15 14 06 50
98 75184 4066402 2004 8 2004 11 05 17 17 43
105 75184 4066402 2004 8 2004 10 05 17 17 43
102 75184 4066402 2004 8 2004 10 04 18 17 43
97 75184 4066402 2004 8 2004 10 04 17 19 50
15 75184 4066402 2004 8 2004 10 04 17 17 43
99 91006 9131800 2004 8 2004 10 24 11 40 04
106 91006 9131800 2004 8 2004 09 24 11 40 04
103 91006 9131800 2004 8 2004 09 23 12 40 04
100 91006 9131800 2004 8 2004 09 23 11 43 14
16 91006 9131800 2004 8 2004 09 23 11 40 04
where the FIX* are the FIXED_VALUES and thee VAR* are the
VARIABLE_VALUES that come from a previous
SUBSTRING(TIMESTAMP_FIELD,X,Y). In this table there's no timestamp
anymore.
The VARIABLE_VALUES stand for
VARE=YEAR
VARF=MONTH
VARG=DAY
VARH=HOUR
VARI=MINUTE
VARJ=SECOND
The self-join you proposed only got one value and unfortunately it
wasn't none from the expected three.
Do you have any idea about what could I do now?
TIA,
David Grant|||Should be :
...
WHERE CAST( t1.day AS CHAR( 10 ) +
CAST( t1.hour AS CHAR( 10 ) +
CAST( t1.minute AS CHAR( 10 ) =
( SELECT MAX( CAST( t1.day AS CHAR( 10 ) +
CAST( t1.hour AS CHAR( 10 ) +
CAST( t1.minute AS CHAR( 10 ) )
...
Anith|||Sorry Anith, but I must be doing something wrong with your solution
because the Query Analyzer is complaining about it. (Something like:
'Multiple columns have been specified in an aggregation expression that
contains an extern reference. If an aggregated expression contains an
extern reference, this must be the only column to be referred in the
expression')
Se han especificado m=FAltiples columnas en una expresi=F3n de agregado
que contiene una referencia externa. Si una expresi=F3n agregada
contiene una referencia externa, =E9sta deber=E1 ser la =FAnica columna a
la que se haga referencia en la expresi=F3n.
SELECT *
FROM REPEATDETECT t1
WHERE CAST(t1.day AS CHAR(10)) +
CAST(t1.hour AS CHAR(10)) +
CAST(t1.minute AS CHAR(10)) =3D
(SELECT MAX(CAST(t1.day AS CHAR(10)) +
CAST(t1.hour AS CHAR(10)) +
CAST(t1.minute AS CHAR(10)))
FROM REPEATDETECT t2
WHERE t2.FIX_VALUE_A =3D t1.FIX_VALUE_A
AND t2.FIX_VALUE_B =3D t2.FIX_VALUE_B
AND t2.FIX_VALUE_C =3D t2.FIX_VALUE_C
AND t2.FIX_VALUE_D =3D t2.FIX_VALUE_D)
Do you know what am I doing wrong?
TIA,
David Grant|||Alejandro,
Your solution is working good. Could you please be so kind to explain
me why does it work? I know you are doing a self-join and using the 2nd
'WHERE' clause to stablish a relationship between the two copies of the
table. However, at this moment I can't guess which is the objective
(apart from solving my problem, thankyou again) of all these
multiplications by 100.
TIA,
David Grant|||> SELECT *
> FROM REPEATDETECT t1
> WHERE CAST(t1.day AS CHAR(10)) +
> CAST(t1.hour AS CHAR(10)) +
> CAST(t1.minute AS CHAR(10)) =
> (SELECT MAX(CAST(t1.day AS CHAR(10)) +
> CAST(t1.hour AS CHAR(10)) +
> CAST(t1.minute AS CHAR(10)))
> FROM REPEATDETECT t2
> WHERE t2.FIX_VALUE_A = t1.FIX_VALUE_A
> AND t2.FIX_VALUE_B = t2.FIX_VALUE_B
> AND t2.FIX_VALUE_C = t2.FIX_VALUE_C
> AND t2.FIX_VALUE_D = t2.FIX_VALUE_D)
Use t2 instead t1 in the aggregation function.
SELECT *
FROM REPEATDETECT t1
WHERE CAST(t1.day AS CHAR(10)) +
CAST(t1.hour AS CHAR(10)) +
CAST(t1.minute AS CHAR(10)) =
(SELECT MAX(CAST(t2.day AS CHAR(10)) +
CAST(t2.hour AS CHAR(10)) +
CAST(t2.minute AS CHAR(10)))
FROM REPEATDETECT t2
WHERE t2.FIX_VALUE_A = t1.FIX_VALUE_A
AND t2.FIX_VALUE_B = t1.FIX_VALUE_B
AND t2.FIX_VALUE_C = t2.FIX_VALUE_C
AND t2.FIX_VALUE_D = t2.FIX_VALUE_D)
AMB
"icebold54@.hotmail.com" wrote:

> Sorry Anith, but I must be doing something wrong with your solution
> because the Query Analyzer is complaining about it. (Something like:
> 'Multiple columns have been specified in an aggregation expression that
> contains an extern reference. If an aggregated expression contains an
> extern reference, this must be the only column to be referred in the
> expression')
> Se han especificado múltiples columnas en una expresión de agregado
> que contiene una referencia externa. Si una expresión agregada
> contiene una referencia externa, ésta deberá ser la única columna a
> la que se haga referencia en la expresión.
>
> SELECT *
> FROM REPEATDETECT t1
> WHERE CAST(t1.day AS CHAR(10)) +
> CAST(t1.hour AS CHAR(10)) +
> CAST(t1.minute AS CHAR(10)) =
> (SELECT MAX(CAST(t1.day AS CHAR(10)) +
> CAST(t1.hour AS CHAR(10)) +
> CAST(t1.minute AS CHAR(10)))
> FROM REPEATDETECT t2
> WHERE t2.FIX_VALUE_A = t1.FIX_VALUE_A
> AND t2.FIX_VALUE_B = t2.FIX_VALUE_B
> AND t2.FIX_VALUE_C = t2.FIX_VALUE_C
> AND t2.FIX_VALUE_D = t2.FIX_VALUE_D)
>
> Do you know what am I doing wrong?
> TIA,
> David Grant
>|||Create an int number with the three values that we can use to compare,
instead comparing them individualy. Let see an example.
var_c = 26
var_d = 17
var_e = 04
((((var_c * 100) + var_d) * 100) + var_e) = 261704
26 * 100 = 2600
2600 + 17 = 2617
2617 * 100 = 261700
261700 + 04 = 261704
var_c = 25
var_d = 17
var_e = 13
((((var_c * 100) + var_d) * 100) + var_e) = 251713
now we can compare 261704 to 251713.
After reading your post to Steve, I would recommend to create a view that
recreate the datetime value, or a calculated column in the same table.
create view dbo.v1
as
select
[ID],
FIXA,
FIXB,
FIXC,
FIXD,
cast(VARE + '-' + VARF + '-' + VARG + 'T' + VARH + ':' + VARI + ':' + VARJ
+ '.000' as datetime) as dt_col
from
dbo.t1
go
Now you can use dt_col to do the comparison.
create view dbo.v1
as
select
[ID],
FIXA,
FIXB,
FIXC,
FIXD,
cast(VARE + '-' + VARF + '-' + VARG + 'T' + VARH + ':' + VARI + ':' + VARJ
+ '.000' as datetime) as dt_col
from
dbo.t1
go
select
FIXA,
FIXB,
FIXC,
FIXD,
[ID]
from
dbo.v1 as a
where
a.dt_col = (select max(b.dt_col) from dbo.v1 where b.FIXA = a.FIXA and
b.FIXB = a.FIXB and b.FIXC = a.FIXC and b.FIXD = a.FIXD)
-- or
select
a.FIXA,
a.FIXB,
a.FIXC,
a.FIXD,
a.[ID]
from
dbo.v1 as a
inner join
(
select
FIXA,
FIXB,
FIXC,
FIXD,
max(dt_col) as max_dt_col
from
dbo.v1
group by
FIXA,
FIXB,
FIXC,
FIXD
) as b
on b.FIXA = a.FIXA and b.FIXB = a.FIXB and b.FIXC = a.FIXC and b.FIXD =
a.FIXD and b.max_dt_col = a.dt_col
go
AMB
"icebold54@.hotmail.com" wrote:

> Alejandro,
> Your solution is working good. Could you please be so kind to explain
> me why does it work? I know you are doing a self-join and using the 2nd
> 'WHERE' clause to stablish a relationship between the two copies of the
> table. However, at this moment I can't guess which is the objective
> (apart from solving my problem, thankyou again) of all these
> multiplications by 100.
>
> TIA,
> David Grant
>