January 9, 2013 at 2:24 am
I have a table like this:
OLD_TABLE:
ID National_Code
----------------------
1 0000000000
2 1111111111
3 1111111111
4 1111111111
5 1111111111
6 2222222222
7 3333333333
I need to insert above National_Code values into new table WITH THIS CONDITION:
National_Code shouldnt repeat more than 3 times.
In other word I need to my new table be like this:
NEW_TABLE:
ID National_Code
----------------------
1 0000000000
2 1111111111
3 1111111111
4 1111111111
5 2222222222
6 3333333333
I already tried this:
INSERT INTO NEW_TABLE[ID,National_Code)
SELECT [ID],[National_Code]
FROM OLD_TABLE
WHERE [National_Code] NOT IN(
SELECT [National_Code]
FROM OLD_TABLE
GROUP BY [National_Code]
HAVING COUNT([National_Code]) > 2
)
But it don't work, because inner HAVING condition return 0 results, and it make outer WHERE condition true and wrongly it insert all repetitive 1111111111 National_Codes.
Thank you for help
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
January 9, 2013 at 3:23 am
Something like this should work, provided the New table is empty.
Create TAble #Old_Table
(Id int, National_Code varchar(20))
Create TAble #New_Table
(Id int, National_Code varchar(20))
Insert into #Old_Table
Values (1,'0000000000')
,(2, '1111111111')
,(3, '1111111111')
,(4, '1111111111')
,(5, '1111111111')
,(6, '2222222222')
,(7, '3333333333')
With Cte AS (
Select
ID
,National_code
, ROW_NUMBER() OVER (Partition by National_Code Order by Id) Rn
From #OLD_TABLE
)
INSERT INTO #New_Table [ID,National_Code)
SELECT [ID],[National_Code]
FROM Cte
Where Rn <=3
If its not an empty table then theres more work to be done.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 9, 2013 at 3:39 am
Thank you very much for help. But NEW_Table is not an empty table 🙂
There might already be some [National_Codes] in New_Table.
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
January 9, 2013 at 3:47 am
Ok, That is what I thought.
The question is do you want to allow duplicates?
For example would you want to have Id's 1,6 and 7 duplicated in the new table if they already exist?
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 9, 2013 at 4:09 am
Heres another way, but it assumes that you dont care if other Rows are duplicated, and will add rows to New_Table Provided the total in New_Table doesnt exceed 3 rows
With Cte AS (
Select
ID
,National_code
, ROW_NUMBER() OVER (Partition by National_Code Order by Id) Rn
From #OLD_TABLE
)
INSERT INTO #New_Table
([ID],National_Code)
SELECT [ID],c.[National_Code]
FROM Cte c
left JOIN (Select National_Code
,Count(*) e_cnt
From #New_Table
Group by National_Code) chk
on chk.National_Code=c.National_Code
Where Rn+ISnull(e_cnt,0)<=3
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 9, 2013 at 4:09 am
ID is not primary-key, Its okay if they duplicate.
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
January 9, 2013 at 4:10 am
masoudk1990 (1/9/2013)
ID is not primary-key, Its okay if they duplicate.
Ok, looks like our posts crossed, the above should work.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 9, 2013 at 4:55 am
Thank you very much.
I couldn't find thanks button any where.
I'm really really thank you. It worked perfectly.
It solved my problem but I want to learn how does it works.
I will trace this query and If I didn't understand it I will ask you for help.
Thank you again 🙂
___________________________________
Computer Enterprise Masoud Keshavarz
I don't care about hell.
If I go there I've played enough Diablo to know how to fight my way out.
January 9, 2013 at 5:04 am
Basically it works by ordering the data in the source table and assiging a Row number to each of the source rows (Select in the CTE)
Then a Left Join with sub select that counts the rows in the Destination table and returns a coutn value.
So if we only want a max of 3 rows, if the Row Number from the first query + the count from the sub select <3 it will insert rows where RN + Count <3.
so
Id 1, 0000000, RN=1
Id 2, 1111111, RN=1
Id 3, 1111111, RN=2
Id 4, 1111111, RN=3
If we have no rows in the destination table, then the count will be NULL (hence the IsNULL(e_cnt,0)), so RN-0<3 will insert all 4 rows.
On a second pass the e_cnt will be 1 for 0000000 and 3 for 1111111, so for 0000000 (RN-1)<3 = true but for 1111111 the e_cnt is 3, so RN-3 <3 is false.
Hope that makes sense.
you can run each the CTE select and sub select on the join individually to see how it works with a populated and unpopulated table.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 9, 2013 at 6:14 am
Hello, I would do as follows there are many others but please verify whether the expected results.
Create TAble #Old_Table
(Id int, National_Code varchar(20))
Create TAble #New_Table
(Id int, National_Code varchar(20))
Insert into #Old_Table
Values (1,'0000000000')
,(2, '1111111111')
,(3, '1111111111')
,(4, '1111111111')
,(5, '1111111111')
,(6, '2222222222')
,(7, '3333333333')
,(8, '2222222222')
,(9, '2222222222')
,(10, '2222222222')
SELECT Id, National_Code
,RANK() OVER
(PARTITION BY National_Code ORDER BY Id DESC) AS nTimes
INTO #Temporal
FROM #Old_Table
ORDER BY National_Code;
INSERT INTO #New_Table(Id, National_Code )
select Id, National_Code from #Temporal
where nTimes <= 3
SELECT * FROM #New_Table
DROP TABLE #Temporal
DROP TABLE #New_Table
DROP TABLE #Old_Table
Results:
Id National_Code
----------- --------------------
1 0000000000
5 1111111111
4 1111111111
3 1111111111
10 2222222222
9 2222222222
8 2222222222
7 3333333333
You're welcome...
Ing. Aneurys Tapia González
DBA + BI Architect
Dominican Republic
atapia@outlook.com | admin@atapia.net
http://sqlservermasbi.wordpress.com
http://atapia.net
January 9, 2013 at 7:20 am
atapia_
Nice piece of code providing the table was blank, but what happens when #New_Table already contains data as per below.
Insert into #New_Table
Values (1,'0000000000')
,(2, '1111111111')
,(3, '1111111111')
,(4, '2222222222')
,(5, '3333333333')
,(6, '2222222222')
the code provided would insert 3 more rows of 111111111, 2222222222 categories, thus breaking the Max of 3 rows per National_code.
The reason for this is that you dont take account of existing rows in New_Table which is a possibility according to the original poster.
_________________________________________________________________________
SSC Guide to Posting and Best Practices
January 9, 2013 at 8:16 am
Really, did not see that consideration.
Thxs!
Ing. Aneurys Tapia González
DBA + BI Architect
Dominican Republic
atapia@outlook.com | admin@atapia.net
http://sqlservermasbi.wordpress.com
http://atapia.net
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply