Help needed with SQL command

Question

Help needed with SQL command

c.reusch

SSC Rookie

Points: 34
More actions
May 10, 2011 at 4:22 am

#250035

Hello,
I have a table:
AccountNo, Period, Amount
100, 11/02, 100
100, 11/03, 110
100, 11/04, 120
110, 11/02, 50
110, 11/05, 70
Now I want to have the latest datasets (No. 3 and No. 5). The problem ist that each account can have another latest dataset. For account 100 the last period 11/04 and for account 110 it is 11/05
Thanks for your help!
Regard, Christian

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

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

Lowell SSC Guru Points: 323518 More actions · Answer 1

c.reusch (5/10/2011)
Hello,
I have a table:
AccountNo, Period, Amount
100, 11/02, 100
100, 11/03, 110
100, 11/04, 120
110, 11/02, 50
110, 11/05, 70
Now I want to have the latest datasets (No. 3 and No. 5). The problem ist that each account can have another latest dataset. For account 100 the last period 11/04 and for account 110 it is 11/05
Thanks for your help!
Regard, Christian

Hi Christian;

this looks a lot like homework, and all the volunteers here want what's best for you: learn the concepts, and not just copy someones answer.

in this case, you are looking for one of the basiscs of SQL: the GROUP BY functionality.

you'll want to read up on the group by functionality, combined with the MAX() function on your period column to get the data you are after; show us what you've tried so far, and we can help you understand the concept better.

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!

srikant maurya Hall of Fame Points: 3293 More actions · Answer 2

Try This

DECLARE @t TABLE(

accountno INT,

period VARCHAR(10),

amount INT)

INSERT INTO @t

(accountno,

period,

amount)

SELECT 100 accountno,

'11/02' period,

100 amount

UNION ALL

SELECT 100,

'11/03',

110

UNION ALL

SELECT 100,

'11/04',

120

UNION ALL

SELECT 110,

'11/02',

50

UNION ALL

SELECT 110,

'11/05',

70

SELECT accountno,

period,

amount

FROM (SELECT Row_number() OVER(PARTITION BY accountno ORDER BY accountno, period DESC)row,

*

FROM @t) t

WHERE row = 1

ORDER BY accountno