InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 21:22:20 GMT20http://www.sqlservercentral.com/Forums/Topic1404612-1292-1.aspxReally, did not see that consideration.Thxs!Wed, 09 Jan 2013 08:16:25 GMTatapia_http://www.sqlservercentral.com/Forums/Topic1404612-1292-1.aspxatapia_Nice piece of code providing the table was blank, but what happens when #New_Table already contains data as per below.[code="sql"]Insert into #New_TableValues (1,'0000000000'),(2, '1111111111'),(3, '1111111111'),(4, '2222222222'),(5, '3333333333'),(6, '2222222222')[/code]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.Wed, 09 Jan 2013 07:20:41 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1404612-1292-1.aspxHello, 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_TableValues (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 nTimesINTO #Temporal FROM #Old_Table ORDER BY National_Code;INSERT INTO #New_Table(Id, National_Code )select Id, National_Code from #Temporalwhere nTimes <= 3SELECT * FROM #New_TableDROP TABLE #TemporalDROP TABLE #New_TableDROP TABLE #Old_Table Results:Id National_Code----------- --------------------1 00000000005 11111111114 11111111113 111111111110 22222222229 22222222228 22222222227 3333333333You're welcome...Wed, 09 Jan 2013 06:14:26 GMTatapia_http://www.sqlservercentral.com/Forums/Topic1404612-1292-1.aspxBasically 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.soId 1, 0000000, RN=1Id 2, 1111111, RN=1Id 3, 1111111, RN=2Id 4, 1111111, RN=3If 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.Wed, 09 Jan 2013 05:04:03 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1404612-1292-1.aspxThank 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 :-)Wed, 09 Jan 2013 04:55:06 GMTmasoudk1990http://www.sqlservercentral.com/Forums/Topic1404612-1292-1.aspx[quote][b]masoudk1990 (1/9/2013)[/b][hr]ID is not primary-key, Its okay if they duplicate.[/quote]Ok, looks like our posts crossed, the above should work.Wed, 09 Jan 2013 04:10:42 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1404612-1292-1.aspxID is not primary-key, Its okay if they duplicate.Wed, 09 Jan 2013 04:09:36 GMTmasoudk1990http://www.sqlservercentral.com/Forums/Topic1404612-1292-1.aspxHeres 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[code="sql"]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_CodeWhere Rn+ISnull(e_cnt,0)<=3[/code]Wed, 09 Jan 2013 04:09:31 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1404612-1292-1.aspxOk, 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?Wed, 09 Jan 2013 03:47:13 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1404612-1292-1.aspxThank you very much for help. But NEW_Table is not an empty table :-)There might already be some [National_Codes] in New_Table.Wed, 09 Jan 2013 03:39:17 GMTmasoudk1990http://www.sqlservercentral.com/Forums/Topic1404612-1292-1.aspxSomething like this should work, provided the New table is empty.[code="sql"]Create TAble #Old_Table(Id int, National_Code varchar(20))Create TAble #New_Table(Id int, National_Code varchar(20))Insert into #Old_TableValues (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[/code]If its not an empty table then theres more work to be done.Wed, 09 Jan 2013 03:23:34 GMTJason-299789http://www.sqlservercentral.com/Forums/Topic1404612-1292-1.aspxI have a table like this:OLD_TABLE:ID National_Code----------------------1 00000000002 11111111113 11111111114 11111111115 11111111116 22222222227 3333333333I need to insert above National_Code values into new table [b]WITH THIS CONDITION:[/b]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 00000000002 11111111113 11111111114 11111111115 22222222226 3333333333I already tried this:[code="sql"]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 )[/code]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 helpWed, 09 Jan 2013 02:24:52 GMTmasoudk1990