join and only include rows where a value is max()?

Question

join and only include rows where a value is max()?

Will Skelton

Valued Member

Points: 67
More actions
October 26, 2006 at 2:57 pm

#69434

lets see if i can get this to make any sense at all
i have an application that has a table that holds active visits to our hospital.
i join the "state" table to the visit table to get current diagnoses for the visit.
the state table has multiple states for each visit so as to track what happened at each visit.
my question is..
is there an easy way withing the join syntax to join only the row in the state table with the maximum value for that visit?
something that might look like this..
.....
inner join state s on v.vID = s.vID AND MAX(s.id)
...
i have been successful doing this with the where clause but i don't think that is an option in this case. I would try and explain how this all fits together but i'm afraid i would fall short of making good sense and everyone would just be lost.
any ideas?
thanks
will
edit: oops.. sorry.. probably should have posted to the T-SQL forum

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Well Will, I think I'm lost anyway. Can you post your DDL for both tables as well as a bit of sample data for each table and your expected result. While your at it, post the query that you have that works for you. Are you just looking for a better way to do it?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Will Skelton Valued Member Points: 67 More actions · Answer 2

well.. i guess it would be a better way, but that isn't why i need it. the other query that does work is really performing a totally different task. it uses ORDER BY and the time field in the state table to get the most recent one. this is a different monster.

maybe this will help.

as the query exists currently i get data like so..

charge   visit    state  diagID  
1        100      101      1
1        100      101      2
1        100      102      1
1        100      102      3
1        100      103      1
1        100      103      2
2        101      104      5
2        101      104      6
2        101      105      5

I'd like to use the join syntax to only pull the most recent (or highest) state id.

in this case for visit 100 it would be state 103 and for visit 101 state 105

see if that gives you enough and i'll be trying to figure out a good way to explain better what i'm doing..

thanks for your help

ws

Will Skelton Valued Member Points: 67 More actions · Answer 3

i guess it is worth mentioning that The result set above is pretty much exactly what i want only i need to get rid of the rows that have rows with a state value that isn't the max state value.

still lost? i might be

ws

Will Skelton Valued Member Points: 67 More actions · Answer 4

maybe i just need to make a temp table with the max values of state and then inner join it instead of the full state table..

sorry.. thinking out loud

Michael Meierruth SSChampion Points: 10051 More actions · Answer 5

This seems to work:

select distinct t.visit,t.state

from t

join

(

select visit,max(state) state

from t

group by visit

) m on t.visit=m.visit and t.state=m.state

Will Skelton Valued Member Points: 67 More actions · Answer 6

fantastic!

thanks Michael.

I think the answer to this question really enforces and reminds me of the fact that tables are sets and can be replaced by sets when needed.

thanks again

will