Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    1

    Unanswered: Self join and join in one query.

    I have two tables, Order and Test, the columns relative to this question are below. They are joined by OrderID.

    Order
    ---------
    OrderID
    VisitID

    Test
    --------
    OrderID
    TestType
    Result
    DateTaken

    I need to retrieve the latest result for 3 test types for a particular VisitID. A test can be taken more than once, so I need to look at DateTaken.

    I am currently using a stored procedure that loops through an array of test types and issues the same query 3 times with a different value for testtype.

    My query looks like this:
    select result from
    (select result
    from test, order
    where testtype = 'TEST1'
    and test.orderid = order.orderid
    and order.visitid = 1)
    where rownum < 2;

    I would like to do this in one sql statement and I would like my result set to be 1 row with 3 columns with the testtype as the column name.

    It seems like there would be a better way of doing this using a self join or subqueries, but I haven't been able to figure it out.

    Can any one offer a solution?

    Thanks in advance!

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    You could use analytics to do it

    select * from
    order o,
    ( -- rank the rows grouping by orderid,testtype in desc order of datetaken
    select OrderID,TestType,Result,DateTaken,
    RANK() OVER (PARTITION BY orderid, testtype ORDER BY datetaken DESC from test
    ) t
    where t.rank=1 -- select the most recent row for each orderid,testtype
    and o.orderid = t.orderid
    and visitid=1

    I think this will work but I havent actually tested it. Anyway you get the gist of the way it works.

    Alan

Posting Permissions

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