Query Assistance - pulling latest info data from table..
-
Hi, I'm new here. Hoping you guys can help me out..
Let's say I have a table of data as per the below..
I'm trying to extract only the green highlighted items..
The rules applied are: Only the latest data concerning all cases, and only 1 line (the latest) per case.
As you can see in the image, I don't want the 2nd,3rd, and 4th record extracted cause they are all superseded by more recent records (identified as they are further in the table).
I've considered using either Distinct or Having? but can't get that to work.. If I could use Distinct but then ensure it's the latest record in the table that would be perfect.
Can anyone assist? I would be most grateful.
Thank You
-
Hi..how do you propose to determine the latest data for CaseID 542345...you show two rows with same opening date?
your spreadsheet displays an order that cannot be determined without another level of detail ...eg incremental IDENTITY column or some other set of "rules" based on possibly "Status"
suggest you provide scripts to create sample table and insert sample data...along with expected results
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
Sounds like a good use for RowNumber() something like this (since I don't have your schema I'm just guessing at some things see the first link in my signature for some tips on posting):
WITH Cases
AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY CaseID ORDER BY LastUpdated DESC) AS RowNo /* Assuming there is a date that is populated when a case is changed */
FROM
dbo.CaseHistory
)
SELECT
*
FROM
Cases
WHERE
Cases.RowNo = 1
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
Jack Corbett (8/29/2014)
Sounds like a good use for RowNumber() something like this (since I don't have your schema I'm just guessing at some things see the first link in my signature for some tips on posting):WITH Cases
AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY CaseID ORDER BY LastUpdated DESC) AS RowNo /* Assuming there is a date that is populated when a case is changed */
FROM
dbo.CaseHistory
)
SELECT
*
FROM
Cases
WHERE
Cases.RowNo = 1
Thanks Jack, but I don't have a 'last updated' field. All I have it the order I it appears in the table, data is appended each time (I didn't design this table).. Is there a way to assign an index (not sure if that's the right term) so that if there are caseID's duplicated, it only pulls the one's furthest down the table (the latest one that's appended).
ps. When typing up the sample table for posting here, I made a mistake and all the jack smith's should have the same caseID.
-
Does the Open column only go to the nearest Day, and not include time?
If not, it looks like you need a way to rank the Status.
For James Turner, what's telling you the Open status should be chosen over the Pending status?
If you can't breakdown the Open date, and/or rank Status by priority...there's not enough information to do what you need.
-
cliffgettings (8/29/2014)
Jack Corbett (8/29/2014)
Sounds like a good use for RowNumber() something like this (since I don't have your schema I'm just guessing at some things see the first link in my signature for some tips on posting):WITH Cases
AS (
SELECT
*,
ROW_NUMBER() OVER (PARTITION BY CaseID ORDER BY LastUpdated DESC) AS RowNo /* Assuming there is a date that is populated when a case is changed */
FROM
dbo.CaseHistory
)
SELECT
*
FROM
Cases
WHERE
Cases.RowNo = 1
Thanks Jack, but I don't have a 'last updated' field. All I have it the order I it appears in the table, data is appended each time (I didn't design this table).. Is there a way to assign an index (not sure if that's the right term) so that if there are caseID's duplicated, it only pulls the one's furthest down the table (the latest one that's appended).
ps. When typing up the sample table for posting here, I made a mistake and all the jack smith's should have the same caseID.
Quick thought, add an Identity column to the table
😎
-
As others have said, you need some way of determining what is the most recent row for each Case. Typically that is a CreateDate or UpdateDate column, or, in your example, it could be that the there is a specific order to the status that can't be overwritten. Often times it matches your clustered index, if you have one.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question -
cliffgettings (8/29/2014)
All I have it the order I it appears in the table, data is appended each timeThere's no such thing as the order of rows in a table. The display order is just whatever order the query processor got the rows in. To do what you ask, there needs to be some column added which specifies the order in which the rows were added. Identity column works, so does a datetime default to getdate()
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply