Query help needed ...

Question

Query help needed ...

Oracle_91

SSChampion

Points: 14548
More actions
November 20, 2007 at 1:41 pm

#179392

Hi pals,
I need small help.
I have a table with data as follows.
select * from test_data
c1 c2 c3
----------------------
111 4 101
122 3 101
133 2 102
144 2 103
155 1 103
Now i need to generate a new column c4 as sequence number in the output
I need to get the output something as follows
c4 c1 c2 c3
-----------------------------
1 111 4 101
2 122 3 101
1 133 2 102
1 144 2 103
2 155 1 103
The newly generated column contains sequence numbers starting from 1 and the sequence should be resetted again to 1 whenever a new c3 value is encountered(for example when the c3 value changes from 101 to 102 the sequence should be resetted to 1).
Can anyone help me out on this regard.
Thanks in advance,
Mahi

Viewing 15 posts - 1 through 14 (of 14 total)

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

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

I understand what you're trying to do... but I don't understand why. I realize you've made some simplified test data and I appreciate that. There are several answers and the "best" one is determined by two things...

1. What is this actually for and why do they need it?

2. How many rows?

--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)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Assuming that you're using SQL 2005 (you are posting this in a SQL 2005 forum), the row number function will do exactly what you want.

select ROW_NUMBER() OVER (PARTITION BY c3 ORDER BY c1) AS c4, c1, c2, c3 from test_data

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 3

Funny thing... you never know if you solved their problem when they don't respond 😉 Pretty rude, too... 😛

--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)

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Gila,

if you give some generic solution to this problem,then it would be useful to everybody who have faced or going to face. Suppose if the server version is 7 or 2000, then you query won't work.I think so.

I think rownumber() function will work only on 2005 not in 2000 or 7.0. I also want to point that rownumber() is the new feature of sql2005.

karthik

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

This is in the SQL 2005 forum and I stated that the solution was for SQL 2005.

Of course it won't work on SQL 2000 or SQL 7. The post was in the 2005 forum, expect a solution that uses SQL 2005 features.

I'm not trying to write a generic solution that will help out everyone on every version of every database that want to do something remotely similar.

The original poster stated a problem, I gave a solution. For that specific problem.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

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

Concur... We'd all be in deep doo-doo if we had to write "generic" solutions that met all needs from SQL Server 6.5 through 2k8.

--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)

Oracle_91 SSChampion Points: 14548 More actions · Answer 7

Oracle_91

SSChampion

Points: 14548

November 26, 2007 at 9:01 am

#754109

Thank you very much frns.

karthik M One Orange Chip Points: 29139 More actions · Answer 8

Gila,

I am not ordering you,Just my kind suggestion.Thats it.Because i am using sql2000,so if you give some generic query then i will try it out.

karthik

Bob Fazio SSChampion Points: 10794 More actions · Answer 9

If you want a 2000 version of the query perhaps you should ask for one (Or offer one yourself) I don't see the point in being critical of a perfectly valid response for a poorly written question. Most people answer questions that are answered with the simplest or best solutions. In this case the one offered was both. What your are asking would be neither.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 10

My question to you is this, why are you looking at SQL 2005 solutions in SQL 2000? This doesn't make sense to me. If a person is using SQL 2005 and asks for help, why wouldn't we use the features available in SQL 2005 to help answer the question? Doesn't make sense to write code that is backward compatible.

😎

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 11

Lynn Pettis (11/27/2007)
Doesn't make sense to write code that is backward compatible.
😎

Watch it Lynn, this one might come back and bite you.... 😉

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Maybe, but I also think it is a valid question. If asked for help in SQL 2005, why would I write something that would also work in SQL 2000 if using SQL 2005 features makes it easier develop and understand?

In my current position I am supporting both SQL 2000 and SQL 2005. If I were to post a question asking for help and needed a solution that worked in both, I'd state that as part of me request for help.

If we aren't asked for a solution that is backward compatible, why would we look for one?

😎

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 13

Don't get me wrong Lynn, I agree with you. I just know how some people will take what you say literally and when it's not in context, it's easy to defeat.

But again I agree with you. don't look for 2000 answers in a 2005 forum. 😀

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 14

karthikeyan (11/27/2007)
Gila,
I am not ordering you,Just my kind suggestion.Thats it.Because i am using sql2000,so if you give some generic query then i will try it out.

If you want a SQL 2000 solution, post a thread in one of the SQL 2000 forums with your requirements and I'll see what I can do.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass