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:
>

Help writing an If else statement

Hello I'm a newbie to programming and need help writing an if
statement.

I have a database set up in SQL with the following fields:

Category Questions Answers

I only want one category to appear for all of the questions and
answers submitted for that category. The way I have it set up now all
if a question is submitted for the same category then the category
will list twice and a one question under each other.

How do I write something if I Dim Category

If it's the same category but a different question just list that
question under that category. If it's a new category list that
category and the questions and answers under thatJJ297 (nc297@.yahoo.com) writes:

Quote:

Originally Posted by

Hello I'm a newbie to programming and need help writing an if
statement.
>
I have a database set up in SQL with the following fields:
>
Category Questions Answers
>
I only want one category to appear for all of the questions and
answers submitted for that category. The way I have it set up now all
if a question is submitted for the same category then the category
will list twice and a one question under each other.
>
How do I write something if I Dim Category
>
If it's the same category but a different question just list that
question under that category. If it's a new category list that
category and the questions and answers under that

I am afraid that I can only answer with the standard recommendation that
you post:

o CREATE TABLE statements for your table(s).
o INSERT statement with sample data.
o The desired output given the sample.

This helps to clarify what you are asking (which I currently do not
understand), and also makes it easy to develop a tested solution.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||JJ297 wrote:

Quote:

Originally Posted by

Hello I'm a newbie to programming and need help writing an if
statement.
>
I have a database set up in SQL with the following fields:
>
Category Questions Answers
>
I only want one category to appear for all of the questions and
answers submitted for that category. The way I have it set up now all
if a question is submitted for the same category then the category
will list twice and a one question under each other.
>
How do I write something if I Dim Category
>
If it's the same category but a different question just list that
question under that category. If it's a new category list that
category and the questions and answers under that

This sounds like it should be done in a separate reporting
layer, using Crystal Reports or something similar.|||Why are you formatting data in the back end? The basic principle of a
tiered architecture is that display is done in the front end and never
in the back end. This a more basic programming principle than just
SQL and RDBMS.

Help with Website Password Login

Im new to website programming, and im currently making a website where i have to make a user login screen. The usernames and passwords, are stored in a SQL Database (SQL Server 2000)..

How do i search the database for the username the user enters and checks the password to see if its right? And what program do you recommend to set this up, the easiest? - Thnaksusing asp u can execute the sql queries to do the check.|||Assuming you use ASP you could do something like this:

Username = TRIM(Request.Form("Username"))
Password = TRIM(Request.Form("Password"))

IF Username <> "" AND Password <> "" THEN
SQL = "SELECT Password FROM users WHERE Username = '" & Username & "'"
Database = "myconnectionstring"
Set MyConn = Server.CreateObject("ADODB.Connection")
MyConn.Open Database

Set RS = MyConn.Execute(SQL)
IF RS.EOF THEN
Response.Redirect("keep_out.asp")
ELSE
dbPassword = TRIM(RS("Password"))
IF Password = dbPassword THEN
Session("mysite_login") = True
Response.Redirect("welcome.asp")
ELSE
Response.Redirect("keep_out.asp")
END IF
END IF
END IF|||Thanks Frettmaestro. Im using an ASP webpage made in Frontpage 2002. Ill try the code, but where do i put it? Do i put it in th cade of the Submit (Login) Button? Thanks again.|||Yes, you probably have a loginpage vith a form. You could put the code from my previous post in the top of that file, and have the form post the data to itself. Then in every page that is supposed to be protected you need to include a file that checks if the login-session has been set or not:

check_user.asp (this is the file you include in all protected files):
<%
IF Session("mysite_login") <> True THEN Response.Redirect("keep_out.asp")
%>

And that's it! You have yourself a "secure" page...|||Im sorry, its not working for me. Do you hava a page you made, that uses that same code? If so, could i have the link, so i can see what your doing that im not? thanks