December 19, 2003 at 12:11 pm
Here is the data in my table.
no name
1 kfd
2 vnj
3 ksdk
4 jfdjs
2 cjsa
2 jsd
3 ajfd
4 hfsdakj
3 gfds
5 hcgs
6 gcds
5 fgshg
5 vfhvdas
8 dgg
8 hfv
8 jdhgj
4 hbf
I wanted to get the rows for first 2 least nos(1,2) and then next least 2(3,4) and the next least two.like that i wnated to get the rows for the every two numbers.can anybody help me in this issue.
Thanks.
December 19, 2003 at 12:36 pm
Do you want these as separate recordsets? Is that what you're looking to do? Or are you looking for them in a particular order as a single recordset?
K. Brian Kelley, GSEC
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
K. Brian Kelley
@kbriankelley
December 19, 2003 at 1:24 pm
I want them in seperate record sets.
Thanks.
December 19, 2003 at 2:12 pm
I feel like I'm over simplifying these things, because someone usually follows up my code plops with a set-based solution.
If you want separate recordsets, though, would this (over-simplified and non-robust code) work for you?
CREATE TABLE #tblSort (
Num int,
Letters varchar(64))
/*
INSERT STUFF
*/
DECLARE @MinOfSet int,
@NextMinOfSet int,
@LastMin int,
@NextMin int,
@MaxOfTable int
SET @MinOfSet = 0 --Assuming a non-zero value for simplicity.
SELECT @MaxOfTable = Max(Num)
FROM #tblSort
SELECT @MinOfSet = Min(Num)
FROM #tblSort
WHILE @MinOfSet <= @MaxOfTable
BEGIN
SELECT @NextMinOfSet = Min(Num)
FROM #tblSort
WHERE Num > @MinOfSet
SELECT *
FROM #tblSort
WHERE Num IN (@MinOfSet,@NextMinOfSet)
ORDER BY Num, Letters
SELECT @MinOfSet = Min(Num)
FROM #tblSort
WHERE Num > @NextMinOfSet
END
HTH,
SJTerrill
DROP TABLE #tblSort
December 19, 2003 at 3:02 pm
Ah, so that's where all those dratted smileys went.
SJTerrill
December 21, 2003 at 11:41 pm
Try this:
DECLARE @n int
SET @n = (
SELECT min(no)
FROM test
)
WHILE @n is not null
BEGIN
SELECT * FROM test WHERE no = @n OR no = @n + 1
/** Choose: (wasn't sure which way you wanted it)
SET @n = (
SELECT min(no)
FROM test
WHERE no >(@n+1)
)
--OR--
SET @n = @n + 2
**/
END
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply