custom sql server

Question

Post reply

custom sql server

Sharon Kumar

Ten Centuries

Points: 1193
More actions
March 3, 2011 at 11:27 am

#388966

Hi this is table values
Investigation_IdCase_IdInvestigation_NoteDateTimeTeam_IdStatus_Id
31101050282Testing Testing Testing 2011-01-26 11:45:00.000 2 1
51101050282sample 2011-01-26 00:00:00.000 3 2
91102010093fdsafsadfasd2011-02-03 00:00:00.000 2 2
111102010093rffas rffas rffas rffas 2011-04-05 00:00:00.000 1 2
131101050215retst fasfsdaf 2011-04-05 00:00:00.000 2 1
I need output like
Investigation_IdCase_IdInvestigation_NoteDateTime Team_Id Status_Id
31101050282Testing Testing 2011-01-26 11:45:00.000 2 1
111102010093rffas rffas rffas rffas 2011-04-05 00:00:00.000 1 2
131101050215retst fasfsdaf 2011-04-05 00:00:00.000 2 1
I need results like caseid which is recently updated.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

You could use a cte with ROW_NUMBER() OVER (PARTITION BY Case_Id ORDER BY DateTime DESC) and then query for row=1.

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Lowell SSC Guru Points: 323518 More actions · Answer 2

i think using the row_number function is the right way to tackle this; note i went to the trouble of putting your data into a consumable format; It helps you in the future if you can provide the data like taht so others can base their volunteer test scripts on it:

With MyCTE(Investigation_Id,Case_Id,Investigation_Note,DateTime,Team_Id,Status_Id)

AS

(

SELECT 3,'1101050282','Testing Testing Testing','2011-01-26 11:45:00.000','2','1' UNION ALL

SELECT 5,'1101050282','sample','2011-01-26 00:00:00.000','3','2' UNION ALL

SELECT 9,'1102010093','fdsafsadfasd','2011-02-03 00:00:00.000','2','2' UNION ALL

SELECT 11,'1102010093','rffas rffas rffas rffas','2011-04-05 00:00:00.000','1','2' UNION ALL

SELECT 13,'1101050215','retst fasfsdaf','2011-04-05 00:00:00.000','2','1'

),

AddingRowNum AS

(

SELECT

row_number() OVER (PARTITION BY Case_Id ORDER BY Case_Id,DateTime DESC) AS RW ,

* FROM MYCTE

)

SELECT * FROM AddingRowNum WHERE RW = 1

ORDER BY Investigation_Id

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!