Need help to create a simplier SQL statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need help to create a simplier SQL statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-01-15 : 07:46:00

Rob W writes "I currently have to do this in asp with 3 sql stmts and do loops. Takes way to long to complet becuase db is getting bigger everyday. I have 3 fields in the database,(RecNo, UserNo, Agreed) I would like to get a count of the UserNo that have a Y in the Agreed column. The catch, UserNo could be in multiple times, agreed can either be Y or N. Each time a user writes to the db, RecNo gets updated by 1. So basically, I need a total # of users who have agreed but only if it is the highest recno for that userno. (There could be more Y and N for a UserNo but shouldn't matter because I only want to count the last entry for that userno if agreed is Y (Are you confused yet?, Hell, Im confused now.)

example:

RecNo UserNo Agreed
1 111 Y
2 222 Y
3 111 N
4 333 Y
5 444 Y
6 111 Y
7 333 N
8 555 Y
9 555 N
10 555 Y

Result should be 4 (111, 222, 444, 555). Thanks in advance"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-01-15 : 07:48:09

This should work, and there is probably a better way to do it:

SELECT A.UserNo FROM myTable A INNER JOIN
(SELECT UserNo, Max(RecNo) AS MaxRec FROM myTable GROUP BY UserNo) B
ON A.UserNo=B.UserNo AND A.RecNo=B.MaxRec
WHERE A.Agreed='Y'

SamC
White Water Yakist

3467 Posts

Posted - 2004-01-15 : 08:39:20

Rob's query is correct, but it returns the rows and you asked for the count.

Here's a revision that's down for the count:

You can get each User's largest Record Number like this: (same as Rob's)

SELECT UserNo, MAX(RecNo) As RecNo
FROM MyTable
GROUP BY UserNo

Then you can INNER JOIN this to MyTable to eliminate redundant rows from each user

SELECT A.*
FROM MyTable A
INNER JOIN (
-- Insert the group by here
) B ON B.UserID = A.UserID AND B.RecNo = A.RecNo

Now you can count just the Y's by adding a where

SELECT COUNT(*) As Ycount
FROM MyTable A
INNER JOIN (
-- Insert the group by here
) B ON B.UserID = A.UserID AND B.RecNo = A.RecNo
WHERE Agreed = 'Y'

Or you can generalize it to return both the Ycount and the Ncount

SELECT SUM(CASE WHEN Agreed = 'Y' THEN 1 ELSE 0 END) As Ycount, SUM(CASE WHEN Agreed = 'N' THEN 1 ELSE 0 END) As Ncount
FROM MyTable A
INNER JOIN (
-- Insert the group by here
) B ON B.UserID = A.UserID AND B.RecNo = A.RecNo

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-01-15 : 21:44:10

I TOLD YOU there would be a better version!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources