Sybase T-SQL Random Sample w/o cursor
-
Working in Sybase T-SQL, I am needing to return say, 5000 of 30,000 rows. Every 6th record perhaps to acheive a true 'sampling' of the complete recordset, rather than the first 5000 records.
Can this be done without using cursor processing & temp table?
thanx
scot derrer - blue cross of idaho
scot derrer -
5000 ramdom records :
Select top 5000 from dbo.YourTable order by NewId() --any random generator that works in sybase
Every sixth record :
Select * from dbo.YourTable TMain inner join
(Select T1.id from dbo.YourTable T1 inner join dbo.YourTable T2 on T1.id <= T2.id group by T1.id having count(*) % 6 = 1) dtIds
on Tmain.id = dtIds.id
-
Strike the 2nd query... this works :
Select TMain.name, dtIds.id, dtIds.Total from dbo.SysObjects TMain inner join
(Select T1.id, count(*) as Total from dbo.SysObjects T1 inner join dbo.SysObjects T2 on T2.id <= T1.id group by T1.id having count(*) % 6 = 1) dtIds
on Tmain.id = dtIds.id
order by dtIds.Total
-
Thank you Remi,
I ran it immediately in MS SQL Server-land and it works great. 82 out of 490 rows returned. Now I will convert it for Sybase T-SQL and the membership data tables. I am optimistic I can get it to work...
Another thing: I am using COUNT in a 1st Select to get the total number of records, then divide that by 5000 to get the actual number (6 in my example) for the sampling of the scenerio I am testing. I have 7 scenerios to do this for and they only want around 5000 records for each sampling scenerio. So the number 6 in my example is actually a variable.
scot derrer -
I don't know in Sybase, but for sql server 2000, you don't have many options for a dynamic row count. You can go with a pourcentage like so :
Select top 17% * from dbo.YourTable order by NewId()
or using set rowcount
Declare @Rowcnt as int
set @Rowcnt = 100
set rowcount @Rowcnt
Select* from dbo.SysObjects
set rowcount 0 --reset to 0 for unlimited other wise all future selects will return only 100 rows (other types of transaction will be affected too).
Also with my 2nd query, you can replace the 6 with a variable
having count(*) % @SomeNumber = 1) dtIds
You could even replace the 1 with a variable to change the sampling even more from one select to the next.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply