Click here to Skip to main content
15,884,298 members
Please Sign up or sign in to vote.
3.67/5 (2 votes)
See more:
I used below querry.
C#
string s = ("Select * INTO [" + textbox1.text+ "NDtemp] FROM ( Select * from [" + textbox1.text+ "markets] order by 1) as NTemp");
                     sqlcommand cmd = new sqlcommand (s, con);
                      if (((cmd.ExecuteNonQuery()) < 0))
                      {

                          MessageBox.Show("Records are not copied to temp table.");
                    }


it is giving me error that: Order by clause is invalid in views,inline functions,derived tables,sub querries and,common sub-querries expressions.

Please help me, I executed this code by using access database but while using sql it is giving me error is there anything I need to change.
Posted

You cannot use an Order By clause in this query at all. Really, databases don't care about record ordering when storing data in the tables. Ordering is only a concern when you're going to present the records to users in some form.
 
Share this answer
 
Comments
hussain548 16-Jan-14 9:44am    
but I used it in access.
Dave Kreskowiak 16-Jan-14 9:46am    
I don't care if you used it on your corn flakes this morning!

There is no reason at all to be using an Order By clause in your query.
joshrduncan2012 16-Jan-14 10:13am    
*thumbs up* nice
ZurdoDev 16-Jan-14 11:25am    
There is 1 reason. If you order the data to match the indexes then it may be faster to do the inserts. When you are dealing with huge volumes it can help.
Dave Kreskowiak 16-Jan-14 16:50pm    
OK, I'll give you a little perf benefit on that one, but we're talking about an Access database, not SQL Server. You still can't use an ORDER BY clause in an SELECT INTO query.
You can use TOP 100 PERCENT in your sql.

C#
string s = ("Select * INTO [" + textbox1.text+ "NDtemp] FROM ( Select TOP 100 PERCENT * from [" + textbox1.text+ "markets] order by 1) as NTemp");
                     sqlcommand cmd = new sqlcommand (s, con);
                      if (((cmd.ExecuteNonQuery()) < 0))
                      {
 
                          MessageBox.Show("Records are not copied to temp table.");
                    }


That is how you can use order by in a subquery.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900