how can i solve this problem?

Question

Post reply

how can i solve this problem?

ashy4blue

Old Hand

Points: 302
More actions
February 5, 2009 at 8:27 pm

#97104

AccountNbrSeqCreateDate
59961101/05/09
59961201/09/09......
59961301/10/09
32187101/05/09.......
32187202/28/09
22195101/10/09
22195201/12/09
22195301/13/09........
22195401/15/09
69248101/11/09.........
69248211/12/09
i want to write a query so that i can display only the rows which are ended by '.....'
that is iam trying to display the row of last but one th row for each specific account no.
Can u guys plz help in developing this code???????????
thanking u...

Viewing 9 posts - 1 through 8 (of 8 total)

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

jchandramouli SSCommitted Points: 1772 More actions · Answer 1

Can you give me a sample output of how the result should appear after querying your table.

-------------------------------------------------------------------------

To know how to ask questions and increase the chances of getting asnwers:

http://www.sqlservercentral.com/articles/Best+Practices/61537/

"I Love Walking In The Rain So No One Can See Me Crying ! " ~ Charlie Chaplin

Mahesh Bote SSCrazy Eights Points: 9107 More actions · Answer 2

[font="Verdana"]Let us know in details what exactly you are trying to do?

Mahesh[/font]

MH-09-AM-8694

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 3

...Not enough information, you should be more specific about what you need, what are the

inputs & what is the expected output. See this article on how to post in the correct way to get better responses

Though from the data, I can guess that you needed the last account ordered by sequence, here is the solution for this...

; WITH Accounts

AS

(

SELECTROW_NUMBER() OVER( PARTITION BY AccountNbr ORDER BY Seq DESC ) AS RowNumber,

AccountNbr, Seq, CreateDate

FROMSomeAccounts

)

SELECTAccountNbr, Seq, CreateDate

FROMAccounts

WHERERowNumber = 1

--Ramesh

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Take another look guys... Ashy wants to return the rows that are second to the last for each account number... 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 5

Jeff Moden (2/6/2009)
Take another look guys... Ashy wants to return the rows that are second to the last for each account number... 😉

Actually, I can't read people's mind:D...Jeff, though, after re-reading, I think may be you were right:).....Thanks for letting me know that.

And for the OP, you just need to change "RowNumber = 1" (for Last) to "RowNumber = 2" (for 2nd Last)

--Ramesh

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

Ramesh (2/6/2009)
Jeff Moden (2/6/2009)
Take another look guys... Ashy wants to return the rows that are second to the last for each account number... 😉
Actually, I can't read people's mind:D...Jeff, though, after re-reading, I think may be you were right:).....Thanks for letting me know that.
And for the OP, you just need to change "RowNumber = 1" (for Last) to "RowNumber = 2" (for 2nd Last)

I've gotten pretty good at mind reading. OP tried to say what (s)he wanted but there's a bit of language barrier problem. Studying the pattern in the example (s)he gave was the key.

You're correct... changing the criteria to "RowNumber = 2" should do it just fine. Nice job.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

StarNamer SSCrazy Eights Points: 8746 More actions · Answer 7

create table #t (

AccountNbr int,

Seq int,

CreateDate varchar(20)

)

insert #t values (59961, 1, '01/05/09')

insert #t values (59961, 2, '01/09/09......')

insert #t values (59961, 3, '01/10/09')

insert #t values (32187, 1, '01/05/09.......')

insert #t values (32187, 2, '02/28/09')

insert #t values (22195, 1, '01/10/09')

insert #t values (22195, 2, '01/12/09')

insert #t values (22195, 3, '01/13/09........')

insert #t values (22195, 4, '01/15/09')

insert #t values (69248, 1, '01/11/09.........')

insert #t values (69248, 2, '11/12/09')

-- If Seq has no missing numbers, use this.

select

t.AccountNbr,

t.Seq,

t.CreateDate

from

#t t

join (

select

AccountNbr,

max(Seq)-1 as 'Seq'

from

#t

group by

AccountNbr

) q

on t.AccountNbr=q.AccountNbr

and t.Seq=q.Seq

-- If Seq is missing some numbers, use this.

;with xt as (

select

AccountNbr,

Seq,

(row_number() over(partition by AccountNbr order by Seq)) as 'TrueSeq' ,

CreateDate

from

#t

)

select

t.AccountNbr,

t.Seq,

t.CreateDate

from

xt t

join (

select

AccountNbr,

max(TrueSeq)-1 as 'TrueSeq'

from

xt

group by

AccountNbr

) q

on t.AccountNbr=q.AccountNbr

and t.TrueSeq=q.TrueSeq

drop table #t

The second version simply create a true sequence if the given one isn't.

[Edit] I had the page open for some time while doing other stuff and didn't see notice that Ramesh's solution works with a minor correction. It's better!

Derek

ashy4blue Old Hand Points: 302 More actions · Answer 8

ashy4blue

Old Hand

Points: 302

February 6, 2009 at 9:51 am

#939644

thank u guys ..u guys done a great job...