using MAX (I think...)

[azzazzello]

quick question, guys

I have a table with, say, 3 values

DATE, COMP_VAL, VALUE

COMP_VAL and VALUE are integer fields and DATE is date

I want to select VALUE where COMP_VAL is the maximum integer for a given set of ranges. How do I do this?

 

[DotNetGnat]

Something like this...

SELECT Date,Comp_Value,Value FROM mytable A
Where A.Comp_Value In (Select Top 1 Comp_Value From mytable B Where A.Value=B.Value Order by Comp_Value DESC)

-DNG

 

[azzazzello]

yea...that would be nice...but by DB is 4.0.8...So no nested selects

 

[DotNetGnat]

how about this:

SELECT A.Date, A.Comp_Value, A.Value
FROM mytable A INNER JOIN (
SELECT Date, Max(Comp_Value) AS Max_Comp FROM MyTable B
) AS M ON A.Value = M.Value

not tested...just tweak it...

-DNG

 

[r937]

DNG, that's still a subquery

azzy, which "given set of ranges"?



r937.com | rudy.ca

 

[azzazzello]

any and all date ranges, rudy.

In fact, you can ignore the DATE field. I just need the value of VALUE where COMP_VAL is the maximum of all the records in the table

 

[r937]

I just need the value of VALUE where COMP_VAL is the maximum of all the records in the table

select t1.COMP_VAL
     , t1.VALUE
  from daTable as t1
cross
  join daTable as t2
group
    by t1.COMP_VAL
     , t1.VALUE
having t1.COMP_VAL = max(t2.COMP_VAL)

r937.com | rudy.ca

 

[DotNetGnat]

thanks for correcting me, Rudy.

-DNG

 

[azzazzello]

thanks rudy,

unfortunately, this is another case of me being too general for my own good . I was hoping I could extend the answer figure out the rest myself, but I cannot . Here is the situation:

I have 2 tables

One of the consists of
ID, attempts, goals

the other consists of

ID, COMP_VALUE, VALUE

The tables are tied by ID

The first table has multiple entries per ID

The second table can have multiple entries per ID.

I need to show a sum of goals, sum of attempts, and the VALUE (from second table) for every ID in the first table
where COMP_VAL is the greatest

so I have this...

Select t1.ID, sum(t1.ATTEMPTS), sum(t1.GOALS), t2.VALUE FROM 
 t1, t2 WHERE t1.ID = t2.ID GROUP BY t1.ID;

and I am not sure how to integrate what you suggested, rudy, into this mess... I tried sticking some sort of a MAX comparison into a HAVING clause, but it's not jiving...

I really appreciate the help

 

[r937]

please show some sample rows of data, and the results you expect

r937.com | rudy.ca

 

[azzazzello]

TABLE 1 

ID | Attempts | Goals

1  |   11     |   5
1  |   9      |   8
2  |   3      |   1
2  |   7      |   4

TABLE 2

ID | COMP_VAL |   VAL

1  |    4     |   5
1  |    3     |   6
1  |    9     |   15
2  |    1     |   7
2  |    3     |   4

The output should be

ID |  SUM(attempts) | SUM (goals) | VALUE

1  |   20           |    13       |   15
2  |   10           |     5       |   4

 

[r937]

your options: upgrade to 4.1 or run several queries into temp tables

r937.com | rudy.ca