Help! Query Question.

Hi all,
I'm new to SQL programming, and I am having a hard time figuring this one ou
t.
I have a table containing the following information:
Activity Cost Account Hours
1 A 100
1 B 200
1 C 250
1 D 100
2 A 600
2 F 200
3 B 100
3 C 200
3 D 400
I would like to create a view that will show the Cost Account with the
highest hours per activity. For the table above, the end result would be
something like this.
Activity Cost Account
1 C
2 A
3 D
I hope this makes sense. Can someone please help me on how to write the SQL
statement for this? Thank you very much in advance!
RogerI'm not sure what you want to do in the case where 2 accounts have the same
number of hours (tied for the most). I'm going to assume you want two rows
returned.
SELECT outer_table.Activity, derived_table.[Cost Account]
From Unnamed_table as outer_table
INNER JOIN
(SELECT Activity, MAX(Hours) as Hours
FROM Unnamed_table
GROUP BY Activity) as derived_table
ON outer_table.Activity = derived_table.Activity
AND outer_table.Hours = derived_table.Hours
ORDER BY Activity
HTH
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Yoyo" wrote:

> Hi all,
> I'm new to SQL programming, and I am having a hard time figuring this one
out.
> I have a table containing the following information:
> Activity Cost Account Hours
> 1 A 100
> 1 B 200
> 1 C 250
> 1 D 100
> 2 A 600
> 2 F 200
> 3 B 100
> 3 C 200
> 3 D 400
> I would like to create a view that will show the Cost Account with the
> highest hours per activity. For the table above, the end result would be
> something like this.
> Activity Cost Account
> 1 C
> 2 A
> 3 D
> I hope this makes sense. Can someone please help me on how to write the S
QL
> statement for this? Thank you very much in advance!
> Roger|||Do:
SELECT t1.activity, t1.Cost
FROM tbl t1
WHERE t1.hours = ( SELECT MAX( t2.hours )
FROM tbl t2
WHERE t2.Activity = t1.Activity )
ORDER BY t1.Activity ;
-- Or
SELECT t1.activity, t1.Cost
FROM tbl t1
INNER JOIN ( SELECT Activity, MAX( hours )
FROM tbl
GROUP BY Activity ) t2 ( Activity, hours )
ON t1.Activity = t2.Activity
AND t1.hours = t2.hours
ORDER BY t1.activity ;
Anith|||Hi Anith,
Thank you for the quick response. I am a little about the SQL
statement. I only have one table and I want to create a view from that
table. But your SQL statement has 2 tables. Can you please clarify? I
think maybe most post wasn't clear. Thank you very much.
Roger
"Anith Sen" wrote:

> Do:
> SELECT t1.activity, t1.Cost
> FROM tbl t1
> WHERE t1.hours = ( SELECT MAX( t2.hours )
> FROM tbl t2
> WHERE t2.Activity = t1.Activity )
> ORDER BY t1.Activity ;
> -- Or
>
> SELECT t1.activity, t1.Cost
> FROM tbl t1
> INNER JOIN ( SELECT Activity, MAX( hours )
> FROM tbl
> GROUP BY Activity ) t2 ( Activity, hours )
> ON t1.Activity = t2.Activity
> AND t1.hours = t2.hours
> ORDER BY t1.activity ;
> --
> Anith
>
>|||Did you try to execute the SQL that either of us posted?
It returns what you want.
Anith gave 2 versions.
The first is called a correlated subquery. But, it basically joining your
table to a sql statement against the same table to figure out the max per
Activity.
The second (which is the same as what I posted) is called a derived table or
inline view. Basically, on the fly create a table that contains the activit
y
and its maximum hours from all rows for that activity. Then join this
derived table with the regular table, to only find the rows that match the
activity and the max hours found in the derived table.
There is no way to do what you want with SQL with only the one table in the
FROM. Instead you need join your table to some sql that gives you some
specific information about your table in this case, you need to figure out
the maximum hours for each activity so that you can figure out the correct
rows to return from your table.
My solution, and both of Anith's solution would work fine as the source of a
view to do exactly what you are trying to do.
Hope this makes sense.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Yoyo" wrote:
> Hi Anith,
> Thank you for the quick response. I am a little about the SQL
> statement. I only have one table and I want to create a view from that
> table. But your SQL statement has 2 tables. Can you please clarify? I
> think maybe most post wasn't clear. Thank you very much.
> Roger
>
> "Anith Sen" wrote:
>|||Hi Ryan,
Thank you for the response. Yes, I want to show both cost accounts if there
is a tie. I am a little about the outer_table and derived_table.
Supposed my table is called t1, how would the SQL be written? Thank you ver
y
much.
Roger
"Ryan Powers" wrote:
> I'm not sure what you want to do in the case where 2 accounts have the sam
e
> number of hours (tied for the most). I'm going to assume you want two row
s
> returned.
> SELECT outer_table.Activity, derived_table.[Cost Account]
> From Unnamed_table as outer_table
> INNER JOIN
> (SELECT Activity, MAX(Hours) as Hours
> FROM Unnamed_table
> GROUP BY Activity) as derived_table
> ON outer_table.Activity = derived_table.Activity
> AND outer_table.Hours = derived_table.Hours
> ORDER BY Activity
> HTH
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Yoyo" wrote:
>|||just replace unnamed_table with t1.
as in
SELECT outer_table.Activity, derived_table.[Cost Account]
From t1 as outer_table
INNER JOIN
(SELECT Activity, MAX(Hours) as Hours
FROM t1
GROUP BY Activity) as derived_table
ON outer_table.Activity = derived_table.Activity
AND outer_table.Hours = derived_table.Hours
ORDER BY Activity
Also, see my other post to you. I tried to explain the derived table and
Anith's correlated subquery to you. Neither of which is easy to explain in
a
couple sentences if you are new.
But, basically here is another attempt
Think of the table within the () as its own table completely separate from
your t1.
(SELECT Activity, MAX(Hours) as Hours
FROM t1
GROUP BY Activity)
This will give you each activity and the max hours for that activity. One
row per activity.
Now you join your table t1 to that new table (which I called derived_table)
on both activity and hours. This helps you identify the rows in t1 that you
are interested in.
Hope this helps clear it up.
--
Ryan Powers
Clarity Consulting
http://www.claritycon.com
"Yoyo" wrote:
> Hi Ryan,
> Thank you for the response. Yes, I want to show both cost accounts if the
re
> is a tie. I am a little about the outer_table and derived_table.
> Supposed my table is called t1, how would the SQL be written? Thank you v
ery
> much.
> Roger
> "Ryan Powers" wrote:
>|||Got it! Thank you very much!!!
"Ryan Powers" wrote:
> Did you try to execute the SQL that either of us posted?
> It returns what you want.
> Anith gave 2 versions.
> The first is called a correlated subquery. But, it basically joining your
> table to a sql statement against the same table to figure out the max per
> Activity.
> The second (which is the same as what I posted) is called a derived table
or
> inline view. Basically, on the fly create a table that contains the activ
ity
> and its maximum hours from all rows for that activity. Then join this
> derived table with the regular table, to only find the rows that match the
> activity and the max hours found in the derived table.
> There is no way to do what you want with SQL with only the one table in th
e
> FROM. Instead you need join your table to some sql that gives you some
> specific information about your table in this case, you need to figure out
> the maximum hours for each activity so that you can figure out the correct
> rows to return from your table.
> My solution, and both of Anith's solution would work fine as the source of
a
> view to do exactly what you are trying to do.
> Hope this makes sense.
> --
> Ryan Powers
> Clarity Consulting
> http://www.claritycon.com
>
> "Yoyo" wrote:
>