February 14, 2013 at 10:16 am
I have a table w/6066 distinct records; each record has total sales column and need to split it into 3 separate tables ranked by spend rate. If I had to split it into 2 tbles I 'd know how to do it by first row_number() ordering by sales desc. Then I would pull to table A every even row and table B every odd row, and this would give me two very closely equally weighted tables based on total sales for each record, however I need a third table also ranked by spend rate, and this is where I'm stumped. I'm completely drawing a blank here and there's a hot deadline on this request.
Any light shed on this is appreciated.
J.
February 14, 2013 at 10:41 am
Basically, use the mod(%) function to split your recs into three like so:
INSERT INTO TABLE_A
SELECT Tableid,sales
FROM (select ROW_NUMBER() OVER ( order by sales) as Tableid,name FROM myTable) tmp
WHERE TableId%3 = 0
INSERT INTO TABLE_B
SELECT Tableid,sales
FROM (select ROW_NUMBER() OVER ( order by sales) as Tableid,name FROM myTable) tmp
WHERE TableId%3 = 1
INSERT INTO TABLE_C
SELECT Tableid,sales
FROM (select ROW_NUMBER() OVER ( order by sales) as Tableid,name FROM myTable) tmp
WHERE TableId%3 = 2
Quick and dirty, but works.
Just include the columns you want in your destination.
February 14, 2013 at 4:56 pm
Very nice, but technically wouldn't remainder 1s be table A, remainder 2s be table B and remainder 0s be table C?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
February 15, 2013 at 7:47 am
Mark, thanks a lot for the solution. It was just what I was looking for to get the results I needed.
Much appreciated,
John
February 15, 2013 at 7:48 am
Good point, Scott. I did some tweaking but in essence that was the direction I needed to take to get the desired result.
J.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply