|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Help! Query Question.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 SQL statement for this? Thank you very much in advance! Roger I'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 Show quote "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 SQL > statement for this? Thank you very much in advance! > > Roger Hi Ryan,
Thank you for the response. Yes, I want to show both cost accounts if there is a tie. I am a little confused about the outer_table and derived_table. Supposed my table is called t1, how would the SQL be written? Thank you very much. Roger Show quote "Ryan Powers" wrote: > I'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 SQL > > statement for this? Thank you very much in advance! > > > > Roger 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. Show quote "Yoyo" 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 confused about the outer_table and derived_table. > Supposed my table is called t1, how would the SQL be written? Thank you very > much. > > Roger > > "Ryan Powers" wrote: > > > I'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 SQL > > > 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 confused 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 Show quote "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 activity 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. Show quote "Yoyo" wrote: > Hi Anith, > > Thank you for the quick response. I am a little confused 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 > > > > > > Got it! Thank you very much!!!
Show quote "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 activity > 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 confused 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 > > > > > > > > >  |
|||||||||||||||||||||||
Other interesting topics