August 2, 2006 at 12:02 pm
Does anyone know how to separate records from a database table into two recordsets in a multiple resultset? I need odd number records (1st, 3rd, 5th, etc) in one recordset and even (2nd, 4th, 6th, etc.) number records in the other recordset.
Thanks for your help!
August 3, 2006 at 5:24 am
try this...
select * from employee where employeeid % 2 = 0
select * from employee where employeeid % 2 0
August 3, 2006 at 5:43 am
but that only works where you have an identity column, or some type of numeric id...
you could addapt something like this for other cases:
SELECT *
FROM
Employee a
WHERE ( SELECT COUNT(*)
FROM Employee b
WHERE a.lastname > b.lastname OR
(a.lastName = b.lastName AND a.firstName >= b.firstName)) % 2 = 0 -- or 0
August 3, 2006 at 7:35 am
The modulo mehtod i.e. where (RecID % 2) is set to equal either 0 or 1 is the most efficient method I know of. If you don't have an identity column already you can insert one and remove it when you're done.
I always include an identity column it makes techniques such as removing duplicate records much, much easier.
Good Hunting
August 7, 2006 at 2:06 pm
hmm... is this your actual application?
I am curious why you would want the odd numbered records separate from the even ones. If it is for display purposes (i.e. You want to put half of the data on one column and half on the other) there are better ways to do it. (I would be more than glad to offer a solution up, if you are more specific).
But if you want to do it this way, I believe that you can use the ROW_NUMBER function in SQL 2005:
select * from
(select row_number() over(order by employee_last) as RowNumber,* from employees) employees_w_row
where RowNumber%2=0 (or =1 for the odd numbered records)
You can then use it to separate into two result sets.
I am Doing it with .Net, are you?
August 7, 2006 at 3:25 pm
i wondered the same thing, whats the application for this...
what happens in sql2k5 when you add 4 rows and deleted the second one. are the last 2 reordered so i have 1 odd row and 2 evens? or would i have 2 odds and one even?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply