Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2004

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

    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(
    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?


  2. #2
    Join Date
    Jul 2004
    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.


  3. #3
    Join Date
    Jan 2004
    Croatia, Europe
    Provided Answers: 5
    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:
    SELECT charge, visit, state, diagID
    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
    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.

  4. #4
    Join Date
    Sep 2004
    If state numbers are unique, i.e., no two visits can have the same state, the following might be what you're looking for:
    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.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts