Help me with this t-sql
-
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
-
SELECT TOP 3 * FROM myTable
ORDER BY Column4, Column1
I hope this helps
-
select distinct will give you this
-
Wrong my friend al rows are diferent
-
Can you describe desired result in words?
What's a logic behind it?
_____________
Code for TallyGenerator -
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.
-
Thanks a lot Farrell......I appreciate it....
It worked......
Thanks,
Ganesh
-
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.
-
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)
Intro to Tally Tables and Functions -
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)
Intro to Tally Tables and Functions
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply