Help me with this t-sql

Question

Post reply

Help me with this t-sql

babuganesh2000

Hall of Fame

Points: 3123

April 6, 2006 at 2:01 pm

#171162

Hi Db Gurus,

I have table with this following data

100000133	100000217	343	100000323
100000133	100000217	343	100000324
100000133	100000217	343	100000325
100000134	100000217	343	100000323
100000134	100000217	343	100000324
100000134	100000217	343	100000325
100000135	100000217	343	100000323
100000135	100000217	343	100000324
100000135	100000217	343	100000325

I wanted to select a query to to display

100000133	100000217	343	100000323
100000134	100000217	343	100000324
100000135	100000217	343	100000325

How do I do this.......

Thanks,

Ganesh

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

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

Felipe Dalorzo SSC Enthusiast Points: 135 More actions · Answer 1

SELECT TOP 3 * FROM myTable

ORDER BY Column4, Column1

I hope this helps

andrewkane17 SSCertifiable Points: 7529 More actions · Answer 2

andrewkane17

SSCertifiable

Points: 7529

April 6, 2006 at 3:34 pm

#631361

select distinct will give you this

Felipe Dalorzo SSC Enthusiast Points: 135 More actions · Answer 3

Felipe Dalorzo

SSC Enthusiast

Points: 135

April 6, 2006 at 3:38 pm

#631362

Wrong my friend al rows are diferent

Sergiy SSC Guru Points: 110208 More actions · Answer 4

Can you describe desired result in words?

What's a logic behind it?

_____________
Code for TallyGenerator

Farrell Keough SSCoach Points: 17414 More actions · Answer 5

The sledge hammer is back!

This will work, but it is somewhat involved; although the request is rather involved as well...

DECLARE @t TABLE( Column1 integer, Column2 integer,Column3 integer,Column4 integer)

DECLARE @PreFinal TABLE( IdentityField integer IDENTITY(1,1),

Column1 integer, Column2 integer,Column3 integer,Column4 integer)

DECLARE @Final TABLE( Column1 integer, Column2 integer,Column3 integer,Column4 integer)

INSERT INTO @t

SELECT 100000133, 100000217, 343, 100000323 UNION ALL

SELECT 100000133, 100000217, 343, 100000324 UNION ALL

SELECT 100000133, 100000217, 343, 100000325 UNION ALL

SELECT 100000134, 100000217, 343, 100000323 UNION ALL

SELECT 100000134, 100000217, 343, 100000324 UNION ALL

SELECT 100000134, 100000217, 343, 100000325 UNION ALL

SELECT 100000135, 100000217, 343, 100000323 UNION ALL

SELECT 100000135, 100000217, 343, 100000324 UNION ALL

SELECT 100000135, 100000217, 343, 100000325

INSERT INTO @PreFinal

SELECT Column1, Column2, Column3, Column4 FROM @t

DECLARE @Counter integer,

@CurrentNumber integer,

@MaxNumber integer,

@DesiredColumn4 integer

SELECT @Counter = 0

SELECT @CurrentNumber = (SELECT MIN( Column1) FROM @t)

SELECT @MaxNumber = (SELECT MAX( Column1) FROM @t)

SELECT @DesiredColumn4 = (SELECT MIN( IdentityField) FROM @PreFinal

WHERE Column1 = @CurrentNumber)

WHILE @CurrentNumber <= @MaxNumber

BEGIN

INSERT INTO @Final

SELECT @CurrentNumber, MIN( pf.Column2), MIN( pf.Column3), m.Column4

FROM @PreFinal pf

INNER JOIN (SELECT Column1, Column4

FROM @PreFinal

WHERE IdentityField = @DesiredColumn4) m ON( pf.Column1 = m.Column1)

WHERE pf.Column1 = @CurrentNumber

GROUP BY m.Column4

SELECT @CurrentNumber = (SELECT MIN( Column1) FROM @t WHERE Column1 > @CurrentNumber)

SELECT @Counter = @Counter + 1

SELECT @DesiredColumn4 = (SELECT MIN( IdentityField) + @Counter

FROM @PreFinal WHERE Column1 = @CurrentNumber)

END

SELECT * FROM @Final

I wasn't born stupid - I had to study.

babuganesh2000 Hall of Fame Points: 3123 More actions · Answer 6

Thanks a lot Farrell......I appreciate it....

It worked......

Thanks,

Ganesh

Farrell Keough SSCoach Points: 17414 More actions · Answer 7

Glad I could help! This is an odd requirment - would you care to be more explicit about why you need these unusual results? As the number of Column1 entries increases, but thier records decrease, this could cause unusual returns...

Thanks

I wasn't born stupid - I had to study.

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

For the data presented, this'll do it... and it doesn't assume that there will only be different 3 rows in the return...

SELECT Col1,Col2,Col3,MAX(Col4) AS Col4
FROM yourtable
GROUP BY Col1, Col2, Col3
ORDER BY Col1, Col2, Col3

If the requirements are a bit different than above, repost and we'll give it a whirl...

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

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