Perplexing Problem with tsql join.

I have been trying to figure this out for some time and I am sure there
must be an easier way than using a cursor. Given the following table
(simplified example):
ID CUSTID DTE
1 1 7/1/05
2 1 9/1/05
3 2 6/1/05
4 2 10/25/06
5 3 5/2/05
6 3 5/2/06
7 4 5/5/05
Return the max dte value record for each custid
So ie the return recordset would contain the following:
ID CUSTID DTE
2 1 9/1/05
4 2 10/25/06
6 3 5/2/06
7 4 5/5/05
Thanks in advance.
PaulYou could have more 1 or more records for each custid in my example
table I forgot to create on custid with 3 records.|||The following would work of course but what if you had another column
that could not be calculated by an aggregate column.
select max(id) id,cid,max(dte) dte from fred group by cid
for example lets add the folowing random values to column rnd
ID CUSTID DTE RND
1 1 7/1/05 32
2 1 9/1/05 42
3 2 6/1/05 68
4 2 10/25/06 2
5 3 5/2/05 5
6 3 5/2/06 9
7 4 5/5/05 3
8 3 6/2/06 7
So the return set would now include
ID CUSTID DTE RND
2 1 9/1/05 42
4 2 10/25/06 2
8 3 6/2/06 7
7 4 5/5/05 3|||Try,
select a.*
from t1 as a inner join (select custid, max(dte) as max_dte from t1 group by
custid) as b on a.custid = b.custid and a.dte = b.max_dte
AMB
"firebalrog" wrote:

> I have been trying to figure this out for some time and I am sure there
> must be an easier way than using a cursor. Given the following table
> (simplified example):
> ID CUSTID DTE
> 1 1 7/1/05
> 2 1 9/1/05
> 3 2 6/1/05
> 4 2 10/25/06
> 5 3 5/2/05
> 6 3 5/2/06
> 7 4 5/5/05
> Return the max dte value record for each custid
> So ie the return recordset would contain the following:
> ID CUSTID DTE
> 2 1 9/1/05
> 4 2 10/25/06
> 6 3 5/2/06
> 7 4 5/5/05
> Thanks in advance.
> Paul
>|||Thanks for the quick response. That is exactly what I needed. I forgot
you could use a query as part of the join. And even if I did I don't
think that I would have thought of using it in that way. Thats
beautiful. Now I will have to go through all my stored procedures and
check for places where I was using a cursor method to look for those
records in that type of situation.
Thanks again.

Perplexing Join

Hi,

I'm having a hard time figuring this out. Let me start by explaining my setup. I have a database that will hold high school football statistics. The two tables to focus on are the games table and the schools table--schema below:

schools
----
school_id (varchar, 4, unique)
school_name (varchar, 32)
school_city (varchar, 32)
school_district (varchar, 32)
games
----
game_id (int, identity, unique)
game_datetime (smalldatetime)
game_stadium (varchar, 32)
team_home (varchar, 4)
team_away (varchar, 4)

I didn't bother creating a separate stadiums table, because games will take place in only two stadiums. But here's what the row(s) I want to look like:

game_datetime, game_stadium, school_name (for team_home), school_name (for team_away)

The trouble is with pulling the school names for both teams from the schools table. I tried this query:

SELECT *FROM gamesINNERJOIN schoolsON team_home = school_idOR team_away = school_id

That pulls two rows for each game (one with the home_team's info and another with the away team's info).

The way the tables are constructed makes sense to me, but I'm not opposed to changing the schema. Perhaps you can point me in a different direction.

Thanks

Try this and see if it gives you the results your looking for

Select g.game_datetime, g.game_stadium, h.school_name, a.school_name

FROM Games g

Inner Join schools h on g.team_home = h.school_id

Inner Join schools a on g.team_away = a.school_id

|||

Oh wow! I didn't know you could do that. That's awesome!

Thanks!

PS: Is this a perfectly normal way to do this, or should I reconsider changing my tables?

|||

your tables are fine. Only thing I would do in changing your tables is to maybe change the names of the team_home | team_away to team_home_id | team_away_id. It has no affect at all on the outcome of your data. But 6 months from now you will know that those two fields are FK relations to school_id in your school table. Which helps from a management point of view.

And yes doing joins like this is perfectly fine.

|||

Thanks! You have no idea how much easier my life just got