September 9, 2011 at 2:11 pm
I have serval historical rows for a case on a history table and I need to get only the most rest updated row on that table for each Case. The history table has a sequence number that indicates the last update 1, 2, 3, 4, 4 being the latest upate. I am trying to use the last sequence number. How do you get the row with the last sequece number?
My query looks like this:
SELECT
c.CASE_ID as 'Case No',
h.DESCRIPTION as 'Description',
FROM CASE_TABLE c
--Get the case history
LEFT JOIN HISTORY_TABLE h ON h.CASE_ID = c.CASE_ID
AND (h.SEQ_NUM = MAX(h.SEQ_NUM))
September 9, 2011 at 2:33 pm
select top 1 [my field here]
from Mytable
join MyHistoryTable on [my join criteria]
order by [My field that tells me which record is the latest].
You really should be using a datetime field instead of a homegrown sequence. It provides a lot of additional functionality with less effort. You know when the record was last modified and if you have it in a not null datetime field with a default of getdate() you don't have to do any work to populate it. You win twice with less effort. 😎
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 9, 2011 at 2:41 pm
Hi Sean,
Thank you for your response. Actually I failed to say that I need to pull the last update for each case, for instance
Case 1 has 2 history updates with sequence 1 and 2. I need sequence 2 for this case
Case 2 has 3 history updates with sequence 1, 2, and 3. I need sequence 3 for this case, so for....
So resultset will show:
CaseNo HistorySeq
Case1 2
Case2 3
September 9, 2011 at 4:29 pm
Fairly simple to do with ROW_NUMBER and the partition by by clause. If you can't figure it out and want some help post your ddl and I can give you a hand on Monday.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 10, 2011 at 5:12 am
You need to use a derived table to select the maximum sequence number and whatever else you need from the history, and join that derived table with the case table (and maybe also with the history_table). Here's two different versions of code to do this. The first version works only if every entry in the history_table for a given case_id has the same description; the second assumes you want the descriptipn from the latest seq number for that case id, and it may be different for other seq numbers for the same case.
select c.case_id, m.description, m.seq_num
from case_table c
left join
(select description, case_id, MAX(seq_num) as seq_num
from history_table
group by case_id,description) m
on m.case_id = c.case_id
select c.case_id, h.description, m.seq_num
from case_table c
left join
(select description, case_id, MAX(seq_num) as seq_num
from history_table
group by case_id,description) m
on m.case_id = c.case_id
left join
history_table h
on h.case_id = m.case_id and h.seq_num = m.seq_num
It seems most likely you will want the second form.
You can add columns to the main select list from case_table from history_table aliased h without problem, but if you add columns to the select list in the object aliased m you also need to add them to the group to list (and the rsults may not be what you want).
Tom
September 10, 2011 at 6:40 pm
Another option is CROSS APPLY. Example:
select c.case_id
,m.description
,m.seq_num
from case_table c
cross apply (select top 1
h.description
,h.case_id
,h.seq_num
from history_table h
where h.case_id = c.case_id
order by seq_num desc) m
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 12, 2011 at 8:22 am
Tom,
Thank you for your help, I was able to use the 2nd query.
Laura
September 12, 2011 at 8:41 am
Jeffry, thank you for your response.
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply