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

waskelton4 · #1 (**permalink**) 10-26-06, 16:40

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..

Code:

.....
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

waskelton4 · #2 (**permalink**) 10-26-06, 17:26

maybe this will help in the understanding of my complicated explanation of a not-so-complicated problem

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

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.

ws

Littlefoot · #3 (**permalink**) 10-28-06, 12:16

As you didn't provide CREATE TABLE statements and INSERT sample data (or query which you used to retreive records showed in your #2 post), let me improvize: query you're looking for might lok like this:

Code:

SELECT charge, visit, state, diagID
FROM TEST t
WHERE t.state = (SELECT MAX(t1.state)
                 FROM TEST t1
                 WHERE t1.visit = t.visit
                );

"TEST" table here is, in fact, result of your query. It could then be

Code:

SELECT charge, visit, state, diagID
FROM (<your_query_here) t
WHERE t.state = ...

I guess this might be done easier (without inline queries) but - do provide something more than you already have.

Peter.Vanroose · #4 (**permalink**) 10-30-06, 02:27

If state numbers are unique, i.e., no two visits can have the same state, the following might be what you're looking for:

Code:

SELECT * FROM test
WHERE state IN (
    SELECT MAX(state) FROM test
    GROUP BY visit )

Be sure to add any additional conditions (limiting the states to be seen) to the inner query, for performance reasons.

Internet Services

Web Development

Digital Marketing

Consumer Tech