Problem with Query

Question

Problem with Query

Chand00

SSCertifiable

Points: 6466
More actions
July 22, 2009 at 7:58 am

#385029

Hi,
Please help me with this query. I want output in the below format. I know that this is not the correct format to post.
table1(NewPart_no,Sno)
table1 sno is idenity
table2(NewPartno,OldPartno,Sno)
In table2 sno is idenity
table2 data
NewPartno OldPartno Sno
1 3 1
1 3 2
1 3 3
1 3 4
2 3 5
3 3 6
1 3 7
1 3 8
2 3 9
2 3 10
when i query for new partno 1 it should give me output in this format
oldPart_no S_no
3 1-4
3 7-8
[font="Verdana"]Thanks
Chandra Mohan[/font]

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

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

I feel compelled to chide you a bit If you know it wasn't the correct format to post in, why didn't you do it the right way? It only takes a minute to copy your data and format it so that I could write a solution and test it. I took the time to do that just now because it looked like a fun problem to solve.

I now have your solution and will post it as soon as you take the time to post the data for your problem correctly. Surely its worth another two minutes of your time.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

matt6288 SSCertifiable Points: 5058 More actions · Answer 2

I am interested in seeing this solution myself. I hope the formatting gets fixed.

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

I'd like to see the solution also. I'd also like the OP to post the code he has tried writing to solve his problem as well, though, before you post your solution ..

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

ChandraMohan Nandula (7/22/2009)
I know that this is not the correct format to post.

Heh... since you don't care, neither do I. Have a nice day. 😉

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

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 5

Matt and Lynn,

I'll go ahead and send you my solution, so you can double-check me. I did it with CTEs and freely admit there is probably a faster performing solution using a temp table or table variable.

Bob

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

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

. (7/22/2009)
Matt and Lynn,
I'll go ahead and send you my solution, so you can double-check me. I did it with CTEs and freely admit there is probably a faster performing solution using a temp table or table variable.
Bob

Heh... guess my name is "mud". 😛 Check your email, guys.

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

Chand00 SSCertifiable Points: 6466 More actions · Answer 7

Hi Friends,

I got this from one of my old friend who is a developer. I dont have the exact structure at the time of posting this as my company does not allow mails from public domains. Anyhow I had generated the sql code for you. Thanks for your help .

CREATE TABLE TABLE1(NewPart_No int, Sno integer identity(1,1))

GO

CREATE TABLE TABLE2(NewPart_No int,OldPartNo int,Sno int idenitty(1,1))

GO

INSERT INTO TABLE1(NEWPART_NO) VALUES(3)

INSERT INTO TABLE1(NEWPART_NO) VALUES(5)

GO

INSERT INTO TABLE2(NEWPART_NO,OLDPARTNO) VALUES(1,3)

INSERT INTO TABLE2(NEWPART_NO,OLDPARTNO) VALUES(2,3)

INSERT INTO TABLE2(NEWPART_NO,OLDPARTNO) VALUES(3,3)

INSERT INTO TABLE2(NEWPART_NO,OLDPARTNO) VALUES(1,3)

INSERT INTO TABLE2(NEWPART_NO,OLDPARTNO) VALUES(2,3)

GO

[font="Verdana"]Thanks
Chandra Mohan[/font]

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

Okay, half way there. What have you (or your friend) done to try and solve this particular problem?

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

This is a fairly complex problem for those that don't know how to solve it and I wouldn't be surprised if no one on Chandra's side of the house tried anything on this one. Chandra jumped through the hoop of providing the necessary test data... I believe it's time to provide the solution. 😉

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

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

Jeff Moden (7/23/2009)
This is a fairly complex problem for those that don't know how to solve it and I wouldn't be surprised if no one on Chandra's side of the house tried anything on this one. Chandra jumped through the hoop of providing the necessary test data... I believe it's time to provide the solution. 😉

Go for it Jeff. Your solution looks to be the best. It just would have been nice to at least see what they had tried first. It would have been an opportunity to see their thought process and show them what they were thinking that was working and what wasn't.

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 11

Hehe, I was goofing around and came up with a solution, albeit poor, that works for the sample data. YMMV:DECLARE @TABLE2 TABLE (NewPart_No int,OldPartNo int,Sno int IDENTITY(1,1))

INSERT INTO @TABLE2(NEWPART_NO,OLDPARTNO) VALUES(1,3)

INSERT INTO @TABLE2(NEWPART_NO,OLDPARTNO) VALUES(2,3)

INSERT INTO @TABLE2(NEWPART_NO,OLDPARTNO) VALUES(3,3)

INSERT INTO @TABLE2(NEWPART_NO,OLDPARTNO) VALUES(1,3)

INSERT INTO @TABLE2(NEWPART_NO,OLDPARTNO) VALUES(2,3)

SELECT

A.oldPartNo AS oldPart_no,

CAST(A.Sno AS VARCHAR(100)) + ' - ' + CAST(B.Sno AS VARCHAR(100)) AS S_no

FROM

(

SELECT

A.Sno,

A.OldPartNo,

ROW_NUMBER() OVER (ORDER BY A.sno ASC) AS RowNum

FROM

(

SELECT *

FROM @Table2

WHERE NewPart_No = 1

) AS A

LEFT OUTER JOIN

(

SELECT *

FROM @Table2

WHERE NewPart_No = 1

) AS B

ON A.Sno = B.Sno + 1

WHERE

B.Sno IS NULL

) AS A

INNER JOIN

(

SELECT

A.Sno,

A.OldPartNo,

ROW_NUMBER() OVER (ORDER BY A.sno ASC) AS RowNum

FROM

(

SELECT *

FROM @Table2

WHERE NewPart_No = 1

) AS A

LEFT OUTER JOIN

(

SELECT *

FROM @Table2

WHERE NewPart_No = 1

) AS B

ON A.Sno + 1 = B.Sno

WHERE

B.Sno IS NULL

) AS B

ON A.RowNum = B.RowNum

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

Short and sweet... just gotta love ROW_NUMBER(). 😛

DECLARE @PartNoToFind INT

SET @PartNoToFind = 1

;WITH

cteGroup AS

(

SELECT ROW_NUMBER() OVER (ORDER BY Sno)

- ROW_NUMBER() OVER (PARTITION BY NewPart_No,OldPartNo ORDER BY Sno) AS MyGroup,

*

FROM Table2

)

SELECT OldPartNo,

Sno = CAST(MIN(Sno) AS VARCHAR(10)) + '-'

+ CAST(MAX(Sno) AS VARCHAR(10))

FROM cteGroup

WHERE NewPart_No = @PartNoToFind

GROUP BY OldPartNo,MyGroup

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

Chand00 SSCertifiable Points: 6466 More actions · Answer 13

Hi Jeff,

Thanks a lot for providing the solution. I will check it and revert back to you.

[font="Verdana"]Thanks
Chandra Mohan[/font]

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 14

I just want to say again that your solution was really pretty Jeff. Mine was also based on a ctes using row_number(), but yours was much faster and more concise. I never thought to use row_number() twice in one statement.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills