Help with a Query

[ejjonesjr]

I have put together a database of my favorite basketball team. Each record is a game played. I wanted to query results on how the team has performed off a loss or off a win or when they were home off 3 straight away games. I assume it would have to be a subquery of some sort, but can not figure one out.

Say my table would look something like this.

ID (primary key), DATE, SITE, TEAM, RESULT, PF, PA

200701 12/01/2007 A Southern W 68 51
200702 12/03/2007 A Northern L 54 58
200703 12/05/2007 A Eastern W 77 76
200704 12/07/2007 H Western L 59 78

How would I query those examples I have listed above which are based off the previous record(s).

Any help from anyone would be greatly appreciated.

Thank you again for time.

[Greg Larsen]

Here are a couple of examples of some subqueries in a where statement that might help you resolve your post.

Greg

set nocount on
create table Results (
ID int,
DATE smalldatetime,
SITE char(1),
TEAM varchar(20),
RESULT char(1),
PF tinyint,
PA tinyint)

insert into Results values(200701,'12/01/2007', 'A', 'Southern', 'W', 68, 51)
insert into Results values(200702, '12/03/2007', 'A', 'Northern', 'L', 54, 58)
insert into Results values(200703, '12/05/2007', 'A', 'Eastern', 'W', 77, 76)
insert into Results values(200704, '12/07/2007', 'H', 'Western', 'L', 59, 78)

-- display first home game after last away game
select top 1 * from Results
where DATE > (select max(DATE) from Results where SITE = 'A')

-- display how team did after they last lose
select top 1 * from Results
where DATE > (select max(DATE) from Results where RESULT = 'W')

drop table Results

[ejjonesjr]

Thank you, but it is returning no results.

Here is the query I am using

SELECT TOP 1 *
FROM test
WHERE date>(SELECT max(date) FROM test WHERE su = 'w');

[Greg Larsen]

What do you get from this query?

SELECT max(date) FROM test WHERE su = 'w'

Are there any records in your "test" table with a date greater then the one returned from the above query? If not that is why your a seeing no results.

[t3chn0n3rd]

I would use really good nested functions

[ejjonesjr]

There are no dates in the table greater than the date I get from this query.

SELECT max(date) FROM test WHERE su = 'w'

What are nested functions?