f



Query performance with order by clause?

Hi all,

Just wondering if anyone can tell me if an order by clause on a select
query would have any impact on the time it takes to retrieve results?

Essentially I'm selecting Top 1 out of a table via various criteria
and currently getting it back without an order by clause. The order by
would only include the column that has the clustered primary index on
it.

Can anyone tell me if in theory this will slow the query down?

Many thanks in advance!

Much warmth,

Murrau
0
4/1/2004 8:57:27 PM
comp.databases.ms-sqlserver 14567 articles. 1 followers. janinepelletier (108) is leader. Post Follow

1 Replies
4080 Views

Similar Articles

[PageSpeed] 11

M Wells (planetquirky@planetthoughtful.org) writes:
> Just wondering if anyone can tell me if an order by clause on a select
> query would have any impact on the time it takes to retrieve results?
> 
> Essentially I'm selecting Top 1 out of a table via various criteria
> and currently getting it back without an order by clause. The order by
> would only include the column that has the clustered primary index on
> it.
> 
> Can anyone tell me if in theory this will slow the query down?

For most situations this is an uninteresting question. TOP 1 with an
ORDER BY means "give me one row, I don't care which", but it's not good
for a random selection.

So if you need your row to be deterministically selected, then you must
have an ORDER BY clause.

The cost for the ORDER BY clause is likely to be marginal, if the order
by columns agrees with the clustered index.


-- 
Erland Sommarskog, SQL Server MVP, sommar@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
0
sommar (1290)
4/1/2004 9:31:14 PM
Reply:

Similar Artilces:

MS Access Database Query
Hi, first of all I have to say I'am new to MS Access, I'm used to database systems like Oracle or MySQL. I created a simple database consisting of a single table and a form to fill in new entrys and search for existing entrys. But the search function that Access generates as I created the proper button is way to simple, I need a function that enables to search in several columns. I saw a few quite simple examples where this was done by DAO, but this doesn't work with a .mdb database, does it? (Access allways replies 'Unknown user datatype' once it gets to the line: 'Dim db As Database') Now, is it possible to generate a new query for .mdb file from a form, or at least with CurrentData Object, or not? And if it is possible with the CurrentData Object is it still a database request then or would it be faster to search through a text file? Regards, Bj�rn Keil Assuming you are running AK2 in the modules you need a reference to the Microsoft DAO 3.6 Object Library So open any VB module Goto Tools->References and select the Microsoft DAO 3.6 Object Library Phil "Bj?rn Keil" <abgrund@silberdrache.net> wrote in message news:e8ef0c96.0311210655.79a6192e@posting.google.com... > Hi, > first of all I have to say I'am new to MS Access, I'm used to database > systems like Oracle or MySQL. > I created a simple database consisting of a single table and a form to > fill in new entrys and search for existing entrys. Bu...

ORDER BY and impact on query performance
Oracle 8i & 9i on Solaris 2.6 and 9 respectively. Sun SPARC hardware. Having got my hints sorted out (thanks guys) I ended up with this query: select /*+ index(wc wd_candidates_pk) index(wwp wd_web_pages_pk) index(wct wd_cand_track_n8)*/ last_name||', '||first_name name, wct.track_date, wc.candidate_id, headline, wwp.web_page_id,wtt2.user_track_type status,wct2.track_date status_date, wct.form_id, wct.response_data_id, wct.track_id from wd_candidates wc, wd_live_candidates wlc, wd_web_pages wwp, wd_cand_track wct, wd_track_types wtt, wd_users wu, wd_cand_track wct2, wd_track_types wtt2 where wlc.web_site_id = 204 and wlc.candidate_id = wc.candidate_id and wc.candidate_id = wct.candidate_id and wct.applied_web_page_id = wwp.web_page_id and wct.track_type_id = wtt.track_type_id AND wtt.system_track_type = 'APPLIC' and wlc.web_page_id = wwp.web_page_id and wu.username = user and wc.candidate_id = wct2.candidate_id and wct2.track_id = (select max(track_id) from wd_cand_track where candidate_id = wc.candidate_id and web_page_id = wwp.web_page_id) and wct2.track_type_id = wtt2.track_type_id and (wwp.owner_user_id = wu.user_id or wwp.owner_user_id in (select user_id_grantor from wd_user_grants ...

Horrible Query Performance, Simple Query -- Performance Tuning Help Needed
Hello all. I'm writing to this group in hopes of a way to fix my problem. So far, no one has been able to fix this issue, they all keep telling me to "deal with it, that is the way it is." I just won't accept that oracle could be this crummy. Anyway, here is my problem: I have a very simple query that does three table joins, a simple filter, and a sort. The query takes 27 seconds to run. From the TKProf output, it looks like most (almost all) of the time is taken doing IO to get the data from disk. I'm unsure of how to make it faster... any and all ideas would be appreciated. Thanks for your help, -- Chad Oracle version 10.2.0.1.0. Brand new hardware running on Unix. Query: SELECT E.EVENT_ID EventId, hdd_tran_2000(D.REPORT_TYPE_NCID) DocumentTitle, D.EVENT_GMTIME DocumentUniversalTime, decode(d.dictating_clinician_name,null,tran_clinician(d.dictating_clinician_ncid,e.event_id),d.dictating_clinician_name) Clinician FROM DOCUMENT D, EVENT E, ebiz_application_document x WHERE E.UNIT_NUMBER= 232411 AND E.EVENT_ID=D.EVENT_ID AND D.REPORT_TYPE_NCID = x.report_type_ncid and x.application_id=1 and x.document_category_id=5 ORDER BY DocumentUniversalTime desc The sizes of each table are: ...

Converting database to MS-SQLServer from PostGRESQL
Forgive me if this question is a bit too generic, if it is, feel free to just not respond. I have a database which has been running in PostgreSQL for a number of years at this stage which I want to port into MS SQL server. It seems that the SQL that Postgre outputs when I do a backup is not syntactically correct within MS-SQL server. My question is, does anyone have any documentation on how to convert a database from the Postgre platform to SQL server? Is it possible using an ODBC connection to import a database structure including table definitions, views etc into SQL Server? Failing this, does anyone have any suggestions on where I might start - I did attempt to go through the SQL code and modify it to suit SQL server, but it's about 3,500 lines of code excluding the insert statements (which themselves are also wrong) and almost every line needs something changed when comparing SQL syntax from Postgre to MSSQL server Thanks in advance for any comments/suggestions. Engada. -- Posted via a free Usenet account from http://www.teranews.com Engada wrote: > I have a database which has been running in PostgreSQL for a number of > years at this stage which I want to port into MS SQL server. > > It seems that the SQL that Postgre outputs when I do a backup is not > syntactically correct within MS-SQL server. pg_dump has a number of flags that may help, e.g. --inserts What specific types of syntax errors do you encounter? Googling (Postgr...

I make Database in Ms Access at order
I make Database in Ms Access at order I good read but week speech and understand speech, If You want take me order please exactly describe working of database stachu from Poland mail: stachuf11@wp.pl phone 048 0505120881 Stachu, A database is an electronic means to store data. Things get much more interesting and complicated from there. I don't speak or write Polish and your English isn't very good. But try being more specific about what your database is for and I'll try to be more helpful. "stachu" <onet_stachuf1@poczta.onet.pl> wrote in message news:63f10725.0406230604.5e7b97ab@posting.google.com... > I make Database in Ms Access at order > I good read but week speech and understand speech, If You want take me > order please exactly describe working of database > stachu from Poland > > > mail: stachuf11@wp.pl > phone 048 0505120881 I make Database in Ms Access at order I good read but week speaking and understand speaking in English , If You want take me order please exactly describe working of database stachu mail: stachuf11@wp.pl phone 048 0505120881 "Alan Webb" <knogeek@hotmail.com> wrote in message news:<NZjCc.583$Kk3.22726@news.uswest.net>... I don't speak or write Polish and > your English isn't very good. Anybody seen Fletcher recently? He might be able to explain it. "Alan Webb" <knogeek@hotmail.com> wrote in message news:NZjCc.583$Kk3....

SQL Query Help-- Order By Clause
Hi I want a simple select query on a column-name (smalldatetime) with values dislayed in desc order with null values FIRST. i.e. Select orderdate from orders order by ( null values first and then orderdate in desc order) could any one please help Thanks Just an idea that might work somehow don't know if its a good way to do it or not but ... order by isnull(cast(datefieldname as varchar(20)),'Z') desc "Muzamil" <muzamil@hotmail.com> wrote in message news:5a998f78.0408120609.526d5bc4@posting.google.com... > Hi > > I want a simple select query on a column-name (smalldatetime) with > values dislayed in desc order with null values FIRST. > > i.e. > > Select orderdate from orders > order by ( null values first and then orderdate in desc order) > > could any one please help > > Thanks On 12 Aug 2004 07:09:16 -0700, Muzamil wrote: >Hi > >I want a simple select query on a column-name (smalldatetime) with >values dislayed in desc order with null values FIRST. > >i.e. > >Select orderdate from orders >order by ( null values first and then orderdate in desc order) > >could any one please help > >Thanks Hi Muzamil, ORDER BY CASE WHEN orderdate IS NULL THEN 1 ELSE 2 END, orderdate DESC Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) > > ORDER BY > CASE WHEN orderdate IS NULL THEN 1 ELSE 2 END, > orderdate DESC > > Best, H...

Updating MS-Access Database query
hello i need to update my MS-Access database thru matlab. when using the following code using function "updates" it updates the database i.e .......whereClause = 'where month = ''Nov'''..... Here "Nov" is an entry in the database. Now when i assign a variable to the months..ie variable name "MNTH" could contain Jan /Feb etc,.....and i want to pass the variable "MNTH" ie .......whereClause = 'where month = ''MNTH'''..... it DOES NOT update the database and i receive the error .....error in horxcat......

query ms access database from the user
At college i have been given this piece of untidy code because it mixes a gui interface with the console, i prefare one or the other, but its not my code, i understand what it does. It opens the database CarDB by using the java odbc (Open Database Connectivity ) driver and searches for the row make with nissan and displays to the screen as text in a gui text box the columns Registration, Model, Year, Price in the same row as the nissan . I would like the programme to ask the user which car their want to search for . Unfortunately i do not learn at college or have taught myself sql. If i am understanding proberly the line ResultSet rec = st.executeQuery( "SELECT Registration, Model, Year, Price FROM Table1 where Make='nissan'"); is where the query starts, but its hardcoded in, i presume i need to have a line similar to ResultSet rec = st.executeQuery( "SELECT Registration, Model, Year, Price FROM Table1 request Make=' '") or something similar, am i along the correct lines? here is the code.. import java.sql.*; //import javax.swing.JOptionPane; //import javax.swing.JTextArea; import java.io.*; public class Car2 { public static void main(String args[]){ Connection connection; Statement st; String out=""; JTextArea display=new JTextArea(); try{ Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); connection= DriverManager.getConnection("jdbc:odbc:CarDB","","");...

How to improve query performance with ,,connect by" clause?
I use CONNECT BY clause but it performs very slow though I use indexes. Any suggests how to speed up the performance? thx in advance > I use CONNECT BY clause but it performs very slow though I use indexes. > Any suggests how to speed up the performance? Please post at least the query and the query plan. On Dec 14, 4:50 am, Dirk Gomez <use...@dirkgomez.de> wrote: > > I use CONNECT BY clause but it performs very slow though I use indexes. > > Any suggests how to speed up the performance?Please post at least the query and the query plan. Yes, posting the actual SQL and the explain plan would be a good step. In the example of the EMP table where empno and manager can be used in a connect by relationship you would generally want an index not just on empno but also on manager. If you have both indexes and the stats are current then the CBO should be able to determine if the indexes or a hash join is the better option. Use of the 9i and up ability to use a connect by clause and perform a to the same table would potentially complicate the SQL but just tune the connect and tune the join. Take a look at the amount of data that you have to process and then estimate the run time and compare that to what you are getting. This will help limit your expectations to reality. HTH -- Mark D Powell -- ...

How to query database with multiple queries
I have the following fieldname in an Access_Table: Field 1 = Cust_ID (Primary key) Field 2 = Date Field 3 = Description Field 4 = Inv_No Field 5 = Amount My SQL Input syntax are: "Select distinct Cust_ID,Date,Description,Inv_No,Amount from Access_Table WHERE Date <Now()-30 And Date >Now()-60 AND Date = Main.Date" It failed to generate the result I want,anyway My "Required" Output is: Cust_ID Date Description Inv_No >30DYS >60DYS 8000 21/05/2004 PC RAM 200 8000 26/06/2004 Modem 180 --------------------------------------------------------------- Total: 180 200 --------------------------------------------------------------- 8001 22/04/2004 Cable 50 8001 23/05/2004 HD 210 --------------------------------------------------------------- Total: 260 --------------------------------------------------------------- Grand Total: 180 460 can anyone help? Thanks From:Cady Steldyn *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Hi You seem to have posted 3 very similar requests. Please see reply to "SQL-Query Access by Date" John "Cady Steldyn" <dcartford@yahoo.ca> ...

I can make databases in Ms Access on order
I can make databases in Ms Access on order my phone number 048 505120881 mail: stachuf11@wp.pl "stachu" spammer@deleted.not wrote > I can make databases in Ms Access on order Please review the FAQ and charter at http://www.mvps.org/access/netiquette.htm. Advertising and employment solicitation are prohibited in USENET newsgroups unless the group's charter specifically permits it. The charter of comp.databases.ms-access does not specifically permit advertising. Larry Linson wrote: > "stachu" spammer@deleted.not wrote > > > I can make databases in Ms Access on order > > Please review the FAQ and charter at > http://www.mvps.org/access/netiquette.htm. Advertising and employment > solicitation are prohibited in USENET newsgroups unless the group's charter > specifically permits it. The charter of comp.databases.ms-access does not > specifically permit advertising. > > > Actually Larry, I thinkl he needs to look at a marketing FAQ, the ad was hardly awe inspiring :-) -- Error reading sig - A)bort R)etry I)nfluence with large hammer On Wed, 07 Jul 2004 08:33:08 +0100, Trevor Best <nospam@localhost> wrote: >Larry Linson wrote: > >> "stachu" spammer@deleted.not wrote >> >> > I can make databases in Ms Access on order >> >> Please review the FAQ and charter at >> http://www.mvps.org/access/netiquette.htm. Advertising and employment &...

export sqlserver database to ms-access by sql script
Hi Is there anyway to export a sqlserver database to ms-access through a sql scipt only without using the sql enterprise manager. -- Message posted via SQLMonster.com http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-general/200601/1 ...

SqlServer 7.0 ODBC error when querying with order by
Hi, we have SQL Server 7.00.961 running on Windows 2000. On the client side we use ODBC driver 3.70.09.61 and an OCX component to retrieve rows from a table having about 600 000 rows. All works fine if we just retrieve rows without ordering them. If we put order by clause to the query, we get the following error message: -2147467259 unspecified error. This error comes almost immediately after submitting the query. If we modify the select clause so that we retrieve only 30 rows, we can use order by without problem : Select column1,... from table --...

Trouble when submitting sql-query to MS Access Database
Hi, I have some problems when trying to submit a simple insertion statement to a MS Access Database. The reading is fine, but when I want to execute an insertion statement using the following code: public void SQLCommand(String sql) throws SQLException { connection = DriverManager.getConnection(database); Statement stmt = connection.createStatement(); stmt.executeUpdate(sql); connection.close(); } gives the following error: java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source) at org.strumpflohner.CodeNotebook.Data.DBManager.SQLCommand(DBManager.java:45) at org.strumpflohner.CodeNotebook.Data.TestClass.main(TestClass.java:15) Could someone help me? kito Suggestion 1: The error is caused because you're referring to a table that does not exist. Try to run the query in a seperate query editor and verify that it does work. Also check that the string you pass as argument is correct, e.g. not concatenation that has destroyed the intended string. Suggestion 2: You probably need to add quotes in the executeQuery. Whenever you pass a string to a database try to inclose the statement with...

Assistance sought re MS Query interrogation of SQL database
I am using MS Query to interrogate the firm's Practice Management Database, which, I am told, is SQL, and return the data to an Excel 2000 spreadsheet. This part of the exercise is successful. The data is retrieved and the desired filters work fine. However, the data then needs to have additional information added to it for subsequent reporting purposes. Unfortunately, I have been unsuccessful in ensuring that the additional data remains "attached" to the retrieved data, as, when the data is refreshed, new rows may inserted amongst the retrieved data, but the manually added data remains static. Is there a way to ensure that any rows inserted by the refreshing of the data also move the additional data? I hope I have made myself clear here. Please forgive me if I am overlooking something blindingly obvious or that has been covered before. Any assistance would be much appreciated. Jamie Roth wrote: > I am using MS Query to interrogate the firm's Practice Management > Database, which, I am told, is SQL, and return the data to an Excel > 2000 spreadsheet. > > This part of the exercise is successful. The data is retrieved and > the desired filters work fine. > > However, the data then needs to have additional information added to > it for subsequent reporting purposes. Unfortunately, I have been > unsuccessful in ensuring that the additional data remains "attached" > to the...

VB code to perform custom query of MS access table and display alert
I have a MS access database which has entries (each of which has a name field) and uses a form to make new entries. I would like to put together code that will run when a new entry's name is entered that does the following: 1) query the database to see if that name is already entered 2) if the name is already present, popup a msgbox telling the user the name is already in the system, and in some way list the results of the query (it is possible to have more than 1 record in the table to have the same name) I think I can do part 1 by running VB code that runs as afterupdate event code, but I'm having trouble finding out how to connect to the access database and perform the query. Any help would be greatly appreciated. George Hadley ghadley_00@yahoo.com On 26 Dec 2005 12:32:31 -0800, ghadley_00@yahoo.com wrote: A column name of "name" is discouraged, because it is a reserved word. Use it at your peril. Below I'm assuming you renamed it to CustomerName, that the name of your control is txtCustomerName, and the name of the table in question tblYourTable. A crude way of doing what you want is to place the following code in the CustomerName_AfterUpdate event: dim lngCount as Long lngCount = DCount("[CustomerName]","tblYourTable","[CustomerName]='" & txtCustomerName & "'") if lngCount > 0 then Msgbox "There are already " & lngCount & " customers by that name in the da...

newbie needs guidance from mastuh on querying mysql database from ms access... please
i've already set up mysql server and msaccess wif myodbc driver (myodbc 3.51) on my computer and i've created a new "database" (it only links to the tables in mysql server) in ms access. The problem comes when i try to do a UNION query from ms access on 3 or more linked mysql tables, it prints me an error... any ideas? It works when i do a union query only on 2 tables but not on more. i've checked that the number of fields in the tables and the field properties of each table are the the same, but i still get that error. thanx for your time "angel duran" <angelduran2004@hotmail.com> wrote in message news:a4e32a26.0408181126.5ee648cc@posting.google.com... > i've already set up mysql server and msaccess wif myodbc driver > (myodbc 3.51) on my computer and i've created a new "database" (it > only links to the tables in mysql server) in ms access. The problem > comes when i try to do a UNION query from ms access on 3 or more > linked mysql tables, it prints me an error... any ideas? It works when > i do a union query only on 2 tables but not on more. i've checked > that the number of fields in the tables and the field properties of > each table are the the same, but i still get that error. thanx for > your time MySQL Server = You get what you pay for. MyODBC Driver = You get what you pay for. Find out what "free" is all about at: http://www.mysql.com/support/techsupport.ht...

Most Performant way of performing sql query
Hi All, I need some help to find an optimal way to perfom the following : Given a table sydsession with column sessionid which contains numeric data between 0 and 65535 problem: I need to find the most performant way of performing the following query - want to select a sessionid between 0 and 65535 that is not present in the table sydsession (sorted from 0 to 65535) The following query works but is extremely slow: SELECT sydsessionid FROM (SELECT sydsessionid FROM (SELECT column_value AS sydsessionid FROM TABLE(CAST(numbers(0,65535) AS number_table))) WHERE sydsessionid NOT IN (SELECT DISTINCT sydsessionid FROM sydsession WHERE sydsessionid BETWEEN 0 AND 65535) ORDER BY sydsessionid) WHERE rownum <= 1; (taking about 6 seconds to execute) In addition, this query also works but is still slow (although faster than the previous): SELECT MIN(sydsessionid) FROM (SELECT sydsessionid from counters MINUS SELECT DISTINCT sydsessionid FROM sydsession WHERE sydsessionid BETWEEN 0 AND 65535); This table uses a temporary table called counters which contains numeric values 0 to 65535. Thanks in advance, Colin C Foy wrote: > Hi All, > > I need some help to find an optimal way to perfom the following : > > Given a table sydsession with column sessionid which contains numeric > data between 0 and 65535 > > problem: I need to find the most performant way of performing the > following query - > want to select a sessionid between 0 and 65535 that ...

How to get Order Number from a query with ORDER BY ?
Hi Gurus, We are developing a pension system where the oldest child of the employee is entitled to the pension, provided he/she is not more than 25 years old. If the oldest child exceed 25 years old he will be removed from the system, the second oldest child replace him and so on.. so its rolling. So we maintain the children table. BUT I don't want to maintain the POSITION column, because I don't want to renumber the position when the oldest exceed 25 years. HOW Can I get the POSITION number on the fly using query statement ?? (I have tried ROWNUM, but it gives me wrong order number) Thank you for your help, xtanto SQL SCript : CREATE TABLE CHILDREN ( EMPNO NUMBER(4), CHILDNAME VARCHAR2(30), BIRTHDATE DATE ); ALTER TABLE CHILDREN ADD CONSTRAINT PK_CHILDREN PRIMARY KEY (EMPNO, BIRTHDATE); Insert into CHILDREN values('5501', 'AAA', '12-JAN-1980'); Insert into CHILDREN values('5501', 'CCC', '12-JAN-1985'); Insert into CHILDREN values('5501', 'BBB', '12-JAN-1990'); select rownum,children.* from children order by birthdate; RESULT : 1,5501,AAA,1/12/1980 3,5501,CCC,1/12/1985 2,5501,BBB,1/12/1990 On 7 Oct 2004 03:13:22 -0700, krislioe@gmail.com (xtanto) wrote: >Hi Gurus, > >We are developing a pension system where the oldest child of the >employee is entitled to the pension, provided he/she is not more than >25 years old. >If the oldest child exceed 25 years ...

How to query database with multiple queries #2
I have the following fieldname in an Access_Table: Field 1 = Cust_ID (Primary key) Field 2 = Date Field 3 = Description Field 4 = Inv_No Field 5 = Amount My SQL Input syntax are: "Select distinct Cust_ID,Date,Description,Inv_No,Amount from Access_Table WHERE Date <Now()-30 And Date >Now()-60 AND Date = Main.Date" It failed to generate the result I want,anyway My "Required" Output is: Cust_ID Date Description Inv_No >30DYS >60DYS 8000 21/05/2004 PC RAM 200 8000 26/06/2004 Modem 180 --------------------------------------------------------------- Total: 180 200 --------------------------------------------------------------- 8001 22/04/2004 Cable 50 8001 23/05/2004 HD 210 --------------------------------------------------------------- Total: 260 --------------------------------------------------------------- Grand Total: 180 460 can anyone help? Thanks From:Cady Steldyn *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! Hi Cady, One problem you're having here is calling a field with the name of "Date" which is a reserved word in Access. Rename this field to DatePaid or Start...

The performance of JOIN query and a query containing a subquery
To optimize the performance, is it better to rewrite a JOIN SQL query as SQL query containing a subquery? On Apr 16, 6:18=A0am, Phper <hi.steven...@gmail.com> wrote: > To optimize the performance, is it better to rewrite a JOIN SQL query > as SQL query containing a subquery? Do you really want to program on ROT? (Rules Of Thumb). You may consider to experiment with your specific SQL statement. So 10046- trace your SQL or use autotrace or use explain plan to see the resource consumption of your query. ...

Ordering results by order of the "IN' clause
Consider this SQL: SELECT my_field FROM my_table WHERE my_field IN ('value2', 'value1', 'value3') Simple enough, but is there anyway to specify that the result should be ordered exactly like the "IN" clause states? So when this recordset comes back, I want it like this: my_field ------------ value2 value1 value3 Possible? Deane (deane.barker@gmail.com) writes: > Consider this SQL: > > SELECT my_field FROM my_table WHERE my_field IN ('value2', 'value1', > 'value3') > > Simple enough, but is there anyway to specify that the result should be > ordered exactly like the "IN" clause states? So when this recordset > comes back, I want it like this: > > my_field > ------------ > value2 > value1 > value3 No. The IN clause is just a syntactic shortcut for a bunch of OR operators. You will need to add explicit ordering, for instance: ORDER BY CASE my_field WHEN 'value2' THEN 1 WHEN 'value1' THEN 2 WHEN 'value3' THEN 3 END -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Thanks. FYI -- I've learned in the meantime that MySQL has this functionality: O...

How to copy table from oracle database to sqlserver database ?
Hello, I need to copy a table from an 8i oracle database to a sqlserver 2000 database. Is it possible to use the command "COPY FROM ... TO ..." ? So, what is the correct syntax ? Thanks for your help Cyril "Cyril" <jewelk@free.fr> wrote in message news:cd38c3d6.0408100617.6f7b9f3e@posting.google.com... > Hello, > > I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > > Is it possible to use the command "COPY FROM ... TO ..." ? > So, what is the correct syntax ? > > > Thanks for your help > > Cyril Look into linked servers in Books Online - you can create a linked server pointing to the Oracle database, then do this: insert into dbo.MSSQLTable select col1, col2, ... from Oracle..SCHEMA.TABLE Alternatively, DTS can move data from Oracle to MSSQL. Simon ...

How to copy table from oracle database to sqlserver database ?
Hello, I need to copy a table from an 8i oracle database to a sqlserver 2000 database. Is it possible to use the command "COPY FROM ... TO ..." ? So, what is the correct syntax ? Thanks for your help Cyril On 10 Aug 2004, jewelk@free.fr wrote: > Hello, > > I need to copy a table from an 8i oracle database to a > sqlserver 2000 database. A few options exist. If this is one-off, just use sqlldr to drop the data to a file and then bcp to get it into SQLServer. > Is it possible to use the command "COPY FROM ... TO ..." ? > So, what is the correct syntax ? Well, I'm sure SQLServer has connectivity to Oracle? If you want to go this route, use that and do this from SQLServer. -- Galen Boyer On 10 Aug 2004 07:15:16 -0700, jewelk@free.fr (Cyril) wrote: >Hello, > >I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > >Is it possible to use the command "COPY FROM ... TO ..." ? >So, what is the correct syntax ? > > >Thanks for your help > >Cyril Read up on using the Heterogeneous Gateway to Sqlserver. -- Sybrand Bakker, Senior Oracle DBA "Cyril" <jewelk@free.fr> wrote in message news:cd38c3d6.0408100615.6371b40e@posting.google.com... > Hello, > > I need to copy a table from an 8i oracle database to a sqlserver 2000 database. > > Is it possible to use the command "...

Web resources about - Query performance with order by clause? - comp.databases.ms-sqlserver

Performance - Wikipedia, the free encyclopedia
A performance , in performing arts , generally comprises an event in which a performer or group of performers behave in a particular way for ...

Julie Stevanja of Stylerunner is speaking at PERFORMANCE INSIGHTS in Sydney this week
Data will be what gives businesses the edge in the 21st century. No aspect of business will be untouched, from decision-making and corporate ...

Finnish Startup Beats Google, Microsoft and Amazon In Cloud Hosting Performance
... performer in a new study of top European Cloud Service Providers by industry consulting agency Cloud Spectator. “We have long identified performance ...

IT teams choose between performance and security
Although security breach stories regularly make the headlines, a new survey shows that the biggest worry IT teams have with current security ...

Utility-Scale PV Projects: First US Statistical Performance Analysis
... photovoltaic operating projects whose empirical AC capacity factors differ by more than a factor of two, however. Understanding the performance ...

Man catches fire during performance at school pep rally in Florida
cleveland.com Man catches fire during performance at school pep rally in Florida (video) cleveland.com DELRAY BEACH, Florida — Eight people ...

Meet the iPhone SE, which packs iPhone 6S performance in a 4-inch body
... it, has an A9 chip and M9 coprocessor, which means it has double the speed of a 5S and is three times faster when it comes to graphics performance. ...

Gun Test: Performance Center Shield
Smith & Wesson has shipped 1,000,000 Shields - This little gem from the Performance Center just might be the King of Concealed Carry

The Passion Live's Best, Worst, and Most Confusing Performances
New York Times The Passion Live's Best, Worst, and Most Confusing Performances E! Online "Hey Jesus, can I get a selfie?" We're genuinely ...

EXCLUSIVE: Justin Bieber Goes on 'Drunk' Rant During Impromptu Performance in Los Angeles: 'I'm Just ...
EXCLUSIVE: Justin Bieber Goes on 'Drunk' Rant During Impromptu Performance in Los Angeles: 'I'm Just Speaking Real S**t'

Resources last updated: 3/22/2016 9:27:15 AM