How do I Join to a Derived Table?

[mwolf00]

I'm working on a larger query. Suddenly I've been asked to add a column to it. The column is has a many to one relationship with the table. How do I return just the top 1 for each row I already have?


SELECT
a.col1
, b.col2
, b.col5
, c.col1
FROM
tbla a
, tblb b
, tblc c
WHERE
a.col1 = b.col2
AND a.col5 = c.col5 AND c.col4 is MAX for that col5

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)

 

[mwolf00]

I created a temp table and joined to it. I don't know if there is a more elegant solution (like T-SQLs in-line derived table)...

Programming today is a race between software engineers striving to build better and bigger idiot-proof programs, and the Universe trying to produce bigger and better idiots. So far, the Universe is winning. - Rick Cook (No, I'm not Rick)