Help with Query

Question

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

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

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 1

Is this you are looking for

declare @test-2 table (Response_ID int, Company varchar(20),Amount decimal(18,2),Quarter tinyint,Year int)

Insert into @test-2

Select 1,'Company A', 100.00, 1, 2010

union all Select 2,'Company A', 101.00, 2, 2010

union all Select 3,'Company B', 102.00, 1, 2010

union all Select 4,'Company B', 103.00, 2, 2010

union all Select 5,'Company B', 104.00, 2, 2010

Select * from @test-2 where Quarter in (Select max(Quarter) from @test-2)

Thanks
Parthi

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

Bob

Have a look at the output from this:

CREATE TABLE #Test ([Response ID] INT, Company VARCHAR(15), Amount MONEY, [Quarter] INT, [Year] INT)

INSERT INTO #Test ([Response ID], Company, Amount, [Quarter], [Year])

SELECT 1, 'Company A', 100.00, 1, 2010 UNION ALL

SELECT 2, 'Company A', 101.00, 2, 2010 UNION ALL

SELECT 3, 'Company B', 102.00, 1, 2010 UNION ALL

SELECT 4, 'Company B', 103.00, 2, 2010 UNION ALL

SELECT 5, 'Company B', 104.00, 2, 2010

SELECT [Response ID], Company, Amount, [Quarter], [Year],

rn = ROW_NUMBER() OVER(PARTITION BY Company, [Quarter] ORDER BY [Response ID] DESC)

FROM #Test

I reckon you want rows where rn = 1.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

mccarthy.bob SSC Enthusiast Points: 167 More actions · Answer 3

Chris thank you that's exactly what I need!

Parthi thanks for your help, I need the latest per quarter so Chris's solution will work better for me

Cheers

Bob