sql querry issue while using orderby clause

Question

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 16-Jan-14 3:21am

hussain548

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Dave Kreskowiak · Answer 1 · 2014-01-16T03:39:00

Solution 1

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.

Posted 16-Jan-14 3:39am

Dave Kreskowiak

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.

ZurdoDev · Answer 2 · 2014-01-16T05:24:00

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.