count records using a max value in another field

Please help. I am struggling.

table

cards year org

Q123 Q MO

Q236 Q MO

Q569 Q MO

R456 R MO

Q789 Q MO

R482 R MO

First I need to find the max value of the year field then use that max value to count the cards associated with that max value where org is a parameter from the user.

So the result would be 2. I need a SQL query to give me these results.

R year is the max value and there are two of those records. I hope this makes sense.

Something like this (works in SQL 2000 and SQL 2005):

Code Snippet

SELECT TOP 1

Year,

Count = count( Year )

FROM MyTable

GROUP BY Year

ORDER BY Year DESC

|||

Code Snippet

SELECT max(activeyear)AS activeyear, COUNT(cardnum) AS cardCnt
FROM camcards

WHERE org = @.org)

This is my original query but it gives me the count for all the years regardless of my max(year). The user would be entering the value in for org. I should get back one line so there is not need to to have an order by.

so in my original message the result should be

activeyear = R and cardCnt = 2

I am sorry Arnie, but your suggestion does not work or make sense to me. I don't think you can put an equal sign in the SELECT part of the statement.

|||

Try:

select a.year, count(*)

from dbo.t1 as a inner join (select c.org, max(c.year) from dbo.t1 as c where c.org = @.org group by org) as b

on a.org = b.org and a.year = b.year

group by a.year

go

select year, count(*)

from dbo.t1

where org = @.org and year = (select max(b.year) from dbo.t1 as b where org = @.org)

order by year

go

select top 1 year, count(*)

from dbo.t1

where org = @.org

gropu by year

order by year desc

go

AMB

|||

Select top 1 year, (SELECT count(1) from table where year = t.yearr and org = t.org)

from table t

where org = 'mo'

order by year desc

|||

Actually, Theresa, the code I provided you does indeed work. I understand that you my not yet have the exprience to follow how it works, and I'll try to help. I've even added the extra WHERE clause to filter for the Org by user input.

Below is demonstration code that creates a sample table (named @.MyTable), and then populates the table with your data (I tossed in a couple of extra rows to verify that they would not be included in the result.) I suggest that you copy and paste code into your query window and execute it to verify that it works, THEN change the code, substituting the table name for your tablename.

I don't think you can put an equal sign in the SELECT part of the statement.

You are a little 'confused' about using equals signs in a SELECT statement, and that is just from inexperience. Actually, there are a couple of ways that one may choose to use equals signs in the SELECT. First, to assign a column value to a variable (e.g., SELECT @.ClientID = ClientID FROM ... ), and also to assign an 'alias' for a 'missing' column name or to rename a column in the resultset (e.g, Count = count( Year ) ). Without the 'alias', the column representing the aggragate value COUNT() would not have a name. That is often OK, and sometimes for illustration purposes, it is useful.

At the bottom is the actual results from executing all of this sample code in my query window. I think that matches what you said that you wanted.

As you progress in your learning SQL Server, you will discover that it is considered a big faux paux to use reserved words as table and column names (e.g., Year). When you do that you may 'confuse' matters, so to prevent such 'confusion', it is necessary to use square brackets ( [ ] ) around those inappropiately named objects. You may wish to refer to Books Online, Topic: Reserved Words.

I hope that this helps you, and good luck with your travels down the SQL Server 'path'.

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
Cards varchar(10),
[Year] char(1),
Org char(2)
)

INSERT INTO @.MyTable VALUES ( 'Q123', 'Q', 'MO' )
INSERT INTO @.MyTable VALUES ( 'Q236', 'Q', 'MO' )
INSERT INTO @.MyTable VALUES ( 'Q569', 'Q', 'MO' )
INSERT INTO @.MyTable VALUES ( 'R456', 'R', 'MO' )
INSERT INTO @.MyTable VALUES ( 'Q789', 'Q', 'MO' )
INSERT INTO @.MyTable VALUES ( 'R482', 'R', 'MO' )
INSERT INTO @.MyTable VALUES ( 'ABC1', 'S', 'NO' )
INSERT INTO @.MyTable VALUES ( 'XYZ2', 'T', 'NO' )

DECLARE @.Org char(2)
SET @.Org = 'MO'

SELECT TOP 1
ActiveYear = [Year],
CardCnt = count( [Year] )
FROM @.MyTable
WHERE Org = @.Org
GROUP BY [Year]
ORDER BY [Year] DESC

ActiveYear CardCnt
- --
R 2

|||Thanks to all who have replied to thread.