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.
| Author |
Topic |
|
lanejc
Starting Member
11 Posts |
 Posted - 2002-04-26 : 17:58:51
|
| I have two tables, one has a large number of records with each record being an entry about a specific job. The other table is a sort of date table that keeps multiple entries for each job and stores a 'stage' for that week. I need to get each record from the first table, as well as the last stage entered from the other table (which would also be the largest value). I thought about using Max, but I can't seem to get it to work like I want.So how would I return all the records from the first table with only the largest 'stage' value from the second table? Each table has a job number to use as a key.Do I need to loop through each record in the second table until I get the largest or is there a function that will work? |
|
|
bm1000
Starting Member
37 Posts |
Posted - 2002-04-26 : 18:33:41
|
| Use a correlated query.e.g. select a.*, b.* from t1 a, t2 b where a.pkey = b.key and b.datecol = ( select max(datecol) from t2 where key = b.key) |
 |
|
|
lanejc
Starting Member
11 Posts |
Posted - 2002-04-26 : 18:56:42
|
| Thanks, that sort of works. It seems to run, but it's incredibly slow. I'm not sure what I can do to speed it up. I was thinking of creating an index on the second table that sorted it by descending value but I'm not sure of the syntax to sort an index on a vlaue in descending order.Anyone know how to do that? Cause if I can sort by the stage column in descending order then I can just get the first value and be done with it.Update: I just tried to create an index. I created it, with the stage column in descending order, but I can't access the index from my SELECT statement. I'm not sure if the system I'm using hasn't actually created the index yet or if I'm just doing something wrong.Edited by - lanejc on 04/26/2002 19:31:33 |
|
|
|
|
|
|
Ok, I can't figure this out