April 23, 2009 at 11:33 am
I am trying to query several tables, one of which I only need the latest note. There are some records that won't have notes. Problem is, my query is excluding those records and I need them. How do I get the max record from the notes table, but still include the original record if it doesn't have a note?
select ltr.littrackingid,ltr.versus, lty.litTypeDesc, loc.attnyName, ltr.entryDate, ltr.status, ln.note
from litigationtracking ltr left outer join
litigationType lty on ltr.littype = lty.littypeid left outer join
litigationOutsideCounsel loc on loc.litCounselID = ltr.counselID left outer join
litigationNotes ln on ltr.litTrackingID = ln.littrackingid
where ln.litNotesID in
(select max(litNotesID) as litNotesID
from litigationNotes
group by litTrackingID)
order by ltr.littrackingid
I know the where clause is limiting me, but I don't know how else to get the max.
April 23, 2009 at 11:35 am
think I just answered my own question. Is this the cleanest way of doing it?
select ltr.littrackingid,ltr.versus, lty.litTypeDesc, loc.attnyName, ltr.entryDate, ltr.status, ln.note
from litigationtracking ltr left outer join
litigationType lty on ltr.littype = lty.littypeid left outer join
litigationOutsideCounsel loc on loc.litCounselID = ltr.counselID left outer join
--litigationNotes ln on ltr.litTrackingID = ln.littrackingid
--where ln.litNotesID in
(select littrackingid, note from litigationnotes where litnotesid in
(select max(litNotesID) as litNotesID
from litigationNotes
group by litTrackingID))as ln on ln.littrackingid = ltr.littrackingid
order by ltr.littrackingid
April 23, 2009 at 3:44 pm
There are several ways to do this. If you are using SQL 2005 or later you might want to look up the ROW_NUMBER function. But, here is one way to do it using a correlated sub-query:SELECT
ltr.littrackingid,
ltr.versus,
lty.litTypeDesc,
loc.attnyName,
ltr.entryDate,
ltr.status,
(
SELECT TOP 1 note
FROM litigationNotes AS ln
WHERE ln.littrackingid = ltr.littrackingid
ORDER BY litNotesID DESC
) AS note
FROM
litigationtracking ltr
LEFT OUTER JOIN
litigationType lty
ON ltr.littype = lty.littypeid
LEFT OUTER JOIN
litigationOutsideCounsel loc
ON loc.litCounselID = ltr.counselID
ORDER BY
ltr.littrackingid
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply