Order By clause

In a sample database, I have the following query.

SELECT Null As Title
FROM tblDummy
UNION ALL SELECT tlkpTitles.Title
FROM tlkpTitles
ORDER BY 1;

tblDummy is a single field table that provides null value to combo boxes 
   and tlkpTitle is also single field table that stores person's titles 
(Mr., Mrs, Ms. etc.). What is the meaning of 1 in the Order by clause? 
Even if I remove the Order By clause altogether, the query returns the 
same results.

Thank you in advance.

- samah
0
samah
8/26/2007 7:30:33 AM
access.queries 6343 articles. 1 followers. Follow

4 Replies
754 Views

Similar Articles

[PageSpeed] 49

samah wrote:
> In a sample database, I have the following query.
> 
> SELECT Null As Title
> FROM tblDummy
> UNION ALL SELECT tlkpTitles.Title
> FROM tlkpTitles
> ORDER BY 1;
> 
> tblDummy is a single field table that provides null value to combo boxes 
>   and tlkpTitle is also single field table that stores person's titles 
> (Mr., Mrs, Ms. etc.). What is the meaning of 1 in the Order by clause? 
> Even if I remove the Order By clause altogether, the query returns the 
> same results.
> 
> Thank you in advance.
> 
> - samah

The ORDER BY clause isn't supposed to alter what is returned, but the
order in which the result is returned.

The ORDER BY clause of Access/Jet allows you to specify ordinal
position, in stead of column name, so in your case, using

ORDER BY Title

Should also work.

-- 
Roy-Vidar
0
RoyVidar
8/26/2007 8:34:07 AM
RoyVidar wrote:
> samah wrote:
>> In a sample database, I have the following query.
>>
>> SELECT Null As Title
>> FROM tblDummy
>> UNION ALL SELECT tlkpTitles.Title
>> FROM tlkpTitles
>> ORDER BY 1;
>>
>> tblDummy is a single field table that provides null value to combo 
>> boxes   and tlkpTitle is also single field table that stores person's 
>> titles (Mr., Mrs, Ms. etc.). What is the meaning of 1 in the Order by 
>> clause? Even if I remove the Order By clause altogether, the query 
>> returns the same results.
>>
>> Thank you in advance.
>>
>> - samah
> 
> The ORDER BY clause isn't supposed to alter what is returned, but the
> order in which the result is returned.
> 
> The ORDER BY clause of Access/Jet allows you to specify ordinal
> position, in stead of column name, so in your case, using
> 
> ORDER BY Title
> 
> Should also work.
> 

Learned something new again! Thank you, Roy.

- samah
0
samah
8/26/2007 9:40:37 AM
"RoyVidar" <roy_vidarNOSPAM@yahoo.no> wrote in message 
news:46d13b01$0$7448$c83e3ef6@nn1-read.tele2.net...
> samah wrote:
>> In a sample database, I have the following query.
>>
>> SELECT Null As Title
>> FROM tblDummy
>> UNION ALL SELECT tlkpTitles.Title
>> FROM tlkpTitles
>> ORDER BY 1;
>>
>> tblDummy is a single field table that provides null value to combo boxes 
>> and tlkpTitle is also single field table that stores person's titles 
>> (Mr., Mrs, Ms. etc.). What is the meaning of 1 in the Order by clause? 
>> Even if I remove the Order By clause altogether, the query returns the 
>> same results.
>>
>> Thank you in advance.
>>
>> - samah
>
> The ORDER BY clause isn't supposed to alter what is returned, but the
> order in which the result is returned.
>
> The ORDER BY clause of Access/Jet allows you to specify ordinal
> position, in stead of column name, so in your case, using
>
> ORDER BY Title
>
> Should also work.

But the only reason why you'd be able to use ORDER BY Title is because the 
first subselect included SELECT Null As Title.

If that had simply been

SELECT Null
FROM tblDummy
UNION ALL
SELECT tlkpTitles.Title
FROM tlkpTitles

then that first column would likely have ended up being named Expr1000, and 
you would have needed to use

SELECT Null
FROM tblDummy
UNION ALL
SELECT tlkpTitles.Title
FROM tlkpTitles
ORDER BY Expr1000


-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)



0
Douglas
8/26/2007 11:45:05 AM
Douglas J. Steele wrote:
> "RoyVidar" <roy_vidarNOSPAM@yahoo.no> wrote in message 
> news:46d13b01$0$7448$c83e3ef6@nn1-read.tele2.net...
>> samah wrote:
>>> In a sample database, I have the following query.
>>>
>>> SELECT Null As Title
>>> FROM tblDummy
>>> UNION ALL SELECT tlkpTitles.Title
>>> FROM tlkpTitles
>>> ORDER BY 1;
>>>
>>> tblDummy is a single field table that provides null value to combo boxes 
>>> and tlkpTitle is also single field table that stores person's titles 
>>> (Mr., Mrs, Ms. etc.). What is the meaning of 1 in the Order by clause? 
>>> Even if I remove the Order By clause altogether, the query returns the 
>>> same results.
>>>
>>> Thank you in advance.
>>>
>>> - samah
>> The ORDER BY clause isn't supposed to alter what is returned, but the
>> order in which the result is returned.
>>
>> The ORDER BY clause of Access/Jet allows you to specify ordinal
>> position, in stead of column name, so in your case, using
>>
>> ORDER BY Title
>>
>> Should also work.
> 
> But the only reason why you'd be able to use ORDER BY Title is because the 
> first subselect included SELECT Null As Title.
> 
> If that had simply been
> 
> SELECT Null
> FROM tblDummy
> UNION ALL
> SELECT tlkpTitles.Title
> FROM tlkpTitles
> 
> then that first column would likely have ended up being named Expr1000, and 
> you would have needed to use
> 
> SELECT Null
> FROM tblDummy
> UNION ALL
> SELECT tlkpTitles.Title
> FROM tlkpTitles
> ORDER BY Expr1000
> 
> 

Thank you, Douglas. I got the point crystal clear now.

- samah
0
samah
8/26/2007 1:54:30 PM
Reply:

Similar Artilces:

purchase order template
I searched the newsgroup for this item and couldn't find it. I would like to find a public domain purchase order template other than what is supplied in my software package. Although one could expect you to do a little better research yourself http://tinyurl.com/w7tj http://tinyurl.com/w7tn -- Regards, Peo Sjoblom "Jock" <anonymous@discussions.microsoft.com> wrote in message news:005b01c3b1e3$31fe2e60$a301280a@phx.gbl... > I searched the newsgroup for this item and couldn't find > it. > > I would like to find a public domain purchase order >...

Order Status 05-31-04
Is there anyway directly or indirectly one can change the status of paid orders that have been converted to invoices and payment received? Because all my Orders are showing 'New' though they have been converted to Invoice and being Paid for already in Full. Thanks and Regard ...

how do i arrange list in alphabetical order?
how do i arrange list in alphabetical order? If each list member is a separate paragraph, select the list and use the Sort command. This is on the Table menu in Word 2003 and earlier (the text does not have to be in a table), and in the Paragraph group on the Home tab in Word 2007. -- Suzanne S. Barnhill Microsoft MVP (Word) Words into Type Fairhope, Alabama USA http://word.mvps.org "Chuks" <Chuks@discussions.microsoft.com> wrote in message news:5EEDD52B-1DC3-413C-9D77-CA20FAFE2A32@microsoft.com... > how do i arrange list in alphabetical order? > ...

Credit hold should not be triggered if order is paid by deposit.
Client wants to be able to use the automatic applying of credit holds on sales orders and invoices. The problem is if the payment terms are COD then the customer has a credit limit of $0.00. This triggers the credit hold automatically on the first line item. But if the payment is entered on the order then technically their credit on that order is also $0.00. However the credit hold has already been applied. The credit department must still go in and remove that hold eventhough there is no credit involved. They are not happy about that. ---------------- This post is a sugges...

Work order problems
Hi, We use RMS (SQL 2000) in a retail environment as well as a POS environment, although this is obviously not recommended. In the POS environment, we use work orders to keep a "tab" of the client's bill until it is eventually tendered off. On a few occasions, we have had some weird things happen with work orders. On a few occasions, open work orders have managed to disappear whilst being saved. I.e. When the waiter adds more items to the work order and saves it, it just disappears. Other times, work orders have frozen up while the waiter was picking up the entire work ...

Hiding Received Items on Purchase Orders
Is there a way to hide already received items on a PO? That is, can a revised PO be printed that only shows remaining open items (backorders). On that same note, is there a way to print out a "Receiver" or "Check-in Sheet" for my staff to check in orders against, as opposed to the actual purchase order, which shows more information than I'd like shown... Thanks for any input!! ...

how can I change the order of trendlines in a legend?
I've got a chart in excell with some trendlines. THey are however in the legend in the wrong order. Is it possible to change the order of the trendlines in the legend? Hi, Looks like the order of trendlines is simply the order they were added and once added, unlike the normal chart series, you can not change the order. Cheers Andy dutchy wrote: > I've got a chart in excell with some trendlines. THey are however in the > legend in the wrong order. Is it possible to change the order of the > trendlines in the legend? -- Andy Pope, Microsoft MVP - Excel http://www.an...

The permissions on <Folder_name> are incorrectly ordered, which may cause some entries to be ineffective (kb 925332)
Hello, Does someone got a solution for my problem ? On a Windows 2003 server Release 2 (+service Pack 2) I got exactly the problem described on http://support.microsoft.com/kb/925332 . I cannot install the patch WindowsServer2003-KB925332-x86-ENU.exe i've received from customer support because it seems "this patch is useless beccause i've already installed service Pack 2" Is there a solution to by-pass this version test and install the needed patch ? Please also note that I'm nout using DFS ;-( Thank you in advance for your replies Thibault RICHARD You nee...

Quantity to Back Order
Customer would like to have a set up in SOP so that quantities on a Quote always get moved to the 'Quantity to Back Order' field instead of the 'Quantity to Invoice' field when transferring to an Order. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. h...

How do I sort by CALENDAR order?
I have a spreadsheet that has a column for Calendar Month Name. I want my data sorted by Calendar Order not alphabetically. The first row should be October, second November, December, January. I've attempted doing this with first key sort order under Sort > Options but it doesn't seem to work. Any ideas? You have to create your own list to do that or use real dates as opposed to text months, you can still format it to look like the name of the month, i.e. for October use 10/01/04 (or whatever year you are working with) then format as mmmm and sort -- Regards, Peo Sjoblom ...

Business Portal SQL Sort order
Hi, Can Business portal be installed for Great Plains 8.0 on SQL server that uses Binary sort order(Latin1_General_BIN) ? During the installation of Sharepoint I had this problem. So I created a separate instance for the content database. But my GP installation I could not change. Thanks in advance Ramesh Ramesh, Thanks for using the newsgroup. Latin1_General_BIN is a supported sql sort order for Business Portal. Thanks, Rob -- Rob Bernhardt Software Quality Assurance Microsoft Business Solutions This posting is provided "AS IS" with no warranties, and confers no rig...

Closing Manufacturing orders
When we close Manufacturing orders, it results in batches - one for each MO - in the GL. 1. How can we set journal entries generated in manufacturing closing to post to GL. Is there any way to do this for MO issue, backflush and receipt too? 2. We have thousands of MOs that have been completed but never closed in the past. Is there any way that I can close these MOs in the backend. I am not interested in the variance entries or issuing of any unissued materials because these MOs are 4-5 years old now. We just want them closed. -Jack Jacktundra@hotmail.com For #2, In Dynamics GP 9 the...

How do you accept deposits on Back Orders?
If I want to sell an item that will go on Back Order, how can I take a full deposit on the sale? When I try to make a sale where a few of the items where placed on Back Order, the Tender window only allows me to accept the exact amount of the items that where available in the store inventory at that moment the sale was taking place. How can I accept the full or partial amount of the entire sale including the Back Order Items? -- Microsoft Partner Never mind, I found it. Its in the Configuration and Options under POS options "Yaniv Zahavi" wrote: > If I want to sell a...

Problem when activating Purchase Order Enhancements
When I tried to activate Purchase Order Approvals (POE is registered), I get the message 'A get/change next operation on table 'POP_PO' is for an invalid key' after the processing is about 30% complete. I found KB article 871751 that says to correct some initialization problems you should drop the POA- and CPO- tables and stored procedures. When you then re-open POE setup the system will recreate everything. I can find the tables, but how do I identify the stored procedures? I've found some POA procedures, but they're prefaced with zDP_. -- Bud Cool, Accountin...

Posted Orders
My customer reported a problem where they were unable to open certain sales orders as the system was saying that the document has already been posted. Having looked into this, it appears to be related to the BCHSOURC field in SOP10100 being set to '**Sales Entry' (note the two leading '*'s). I've set BCHSOURC back to 'Sales Entry' for a couple of the documents and this 'appears' to solve the problem, however I'd like to understand this problem a little better before I update them all. SOP10100 currently contains 8500 documents of which 310 have BCHSOU...

This Document has been removed from Sales Order Processing History
Hi I have imported data from RM/SOP (GPv10 SP2). All the history and all the transactions were imported Now I am trying to using Sales Inquiry from Inquiry >> Sales >> Transaction by Customer. Selected a customer, all details appeares including invoices, payments and other documents. But when I try to drill down on one SOP invoice I get above mentioned error. I have already re-created the Key master file by deleting the RM00401 and running the check links. This table has been re-populated. But still this problem exists. Does any have any idea to resolve this problem...

IM with Sales order
I am running GPS 8 with IM 8, I am integrating sales order through IM from text file, now the problem: Doc 1 Error: The default site is not assigned to item xxxx I do assign it in the Inventory Integration specifically in: Inventory Item>Qtys/Sites Field=Default Site ID Rule=Use Constant Source=WAREHOUSE DOC 2 ERROR: There is no default U Of M for item xxxxxx Please Help me, NanyG. NanyG: In GP Inventory Control, you have to assign an item as being "stocked" at the site before you can add item quantities to the site or sell items from the site. Do this via Cards - Inve...

Send form windows to the back of -global- Z-order
I know there is a function to send child form windows to the back of an app's 'local' Z-order. And there's an easy way to send them to the -front- of the global Z-order. But is there any way to send them to the back of the global Z-order? In other words, place the form windows behind all other currently active windows? Bob wrote: > I know there is a function to send child form windows to the back of > an app's 'local' Z-order. And there's an easy way to send them to the > -front- of the global Z-order. > > But is there any way to se...

Outlook 2003
Hi Is it possible to order the deleted items by date of deletion in outlook 2003? Many thanks Lee Lee Atkinson <leeatkinsonlincs@hotmail.com> wrote: > Is it possible to order the deleted items by date of deletion in > outlook 2003? Use the Field Chooser to add the Modified time to the Deleted Items folder header bar (the one that says "From", "Subject", "Received", etc. and sort on that time. Deleting an item is a modification, so I would expect the modified date to be affected, giving you the most accurate value of the item's deleted time....

Printing in reverse order
When I try to print from excel and want the apges to print in reverse order (i.e first page of document printed last) even if I select this feature from the print setup exel still prints the first page first!! Is there a fix available?? I use Office XP (2002) and HP printers Thank you Printing options available in Excel are limited by the available connected printer(s). Any issues with printing are generally associated to the printer and drivers installed, not with Excel itself. Verify your printer actually supports reverse printing and that you have the latest drivers installed for ...

Duplicate in last 100 Orders
I need to generate a warning that a [Container#] has been entered within the last 100 orders [REF#] I need the user to then choose whether to accept the entry or re-enter the data. My assumption is this code can be entered on the from at the "After Update" event. Suggestions please. -- A MACKENZIE, CMA, MBA UNTESTED UNTESTED Have "After Update" event call macro with this condition to display message -- (SELECT TOP 100 Count([Container#]) FROM YourTable HAVING [YourTable].[Container#] = [Forms]![YourFormName]![YourTextBox] ORDER B...

Character sort order ?
I have been trying to make sense out of how XL sorts my text. The help file is clear on this ... and yet it sorts "[" before "1" (which is not the stated default order). So ... apart from special defined lists, is there an option to turn this default order off ... or to change it in some way ? ... or am I missing something really simple here ? :) Thanks Bruce ---------------------------------------- I believe you find life such a problem because you think there are the good people and the bad people. You're wrong, of course. There are, always and only, the bad...

Pivot Table Is Not in Order
I have been given a pivot table that already had six years of data in it, and was told to add four more years data to it. I went into the source document and added everything that needed to be added in the correct places, and then refreshed the pivot table. The pivot table added all of the new data correctly except for one thing - it isn't in order. The data for years 1997 - 2003 are fine, but then the years 2004 - 2007 show up out of order (2005, 2006, 2004, 2007). Being a beginner at pivot tables still, I just can't figure this out. Has anyone ever had this problem before and c...

Delete subforms from tab order
I have a form containing 2 subforms. These subfoms are not allowed to be edited. Now the tab order goes from the form to the first subform and stays there. Is there any way to remove the 2 subforms from the tab order so it will just tab through the form and go to the next form. Like it would if it contained no subforms? On the subform property sheet, on the Other tab set "tab stop" to "No" -- hth, SusanV "sgtschultz1981" <sgtschultz1981@discussions.microsoft.com> wrote in message news:3BB95F26-E2F9-4BF2-9AD2-274E4FE81E2D@microsoft.com... >I have a...

Modify back order transfer to order process
Got a question for anyone who is familiar with the transfer process from back order to order. I'd like to make a modification in that process to clear the PICTICNU and PCKSLPNO fields in SOP10100 for the new SOP order created. Currently those fields retain the values from the original SOP order. Has anyone made a similar mod? Any pointers? Thanks. -- Jim@TurboChef Jim - I am assuming you are on Version 8 or higher. Go to Tools - Setup - Sales - Sales Order Processing On this window, click on the numbers button - the last two options are Packing Slip and Picking Ticket. You pr...