Whats the difference? (1 Viewer)

prabha_friend · May 25, 2015

SELECT [Detail Total Profit MTD].SR_NO, [Detail Total Profit MTD].ID_PARTY, [Detail Total Profit MTD].[Issuer Name], [Detail Total Profit MTD].[Issuer#], [Total Revenue]+[Total Expense] AS [Net Income], [Cancellation Fees]+[Issuance Fees]+[Cancellation Fees GDN]+[Issuance Fees GDN]+[Other Revenues*]+[Dividend Fees]+[Tax Reclaim Service]+[Depository Fees]+[OOP Recoveries]+[Broker Reclaims]+[MAX Revenues*]+[FX Fees]+[Pink Sheets]+[NII Revenues*] AS [Total Revenue], [Contrib# Expenses*]+[OOP Expenses*]+[Transfer Agency*]+[Other Expenses*] AS [Total Expense], [Total Revenue]+[Contrib# Expenses*] AS [Total Revenue-Contribution Expense], [Detail Total Profit MTD].[Cancellation Fees], [Detail Total Profit MTD].[Issuance Fees], [Detail Total Profit MTD].[Cancellation Fees GDN], [Detail Total Profit MTD].[Issuance Fees GDN], [Detail Total Profit MTD].[Other Revenues*], [Detail Total Profit MTD].[Dividend Fees], [Detail Total Profit MTD].[Tax Reclaim Service], [Detail Total Profit MTD].[Depository Fees], [Detail Total Profit MTD].[OOP Recoveries], [Detail Total Profit MTD].[Broker Reclaims], [Detail Total Profit MTD].[MAX Revenues*], [Detail Total Profit MTD].[FX Fees], [Detail Total Profit MTD].[Pink Sheets], [Detail Total Profit MTD].[NII Revenues*], [Detail Total Profit MTD].[Contrib# Expenses*], [Detail Total Profit MTD].[OOP Expenses*], [Detail Total Profit MTD].[Transfer Agency*], [Detail Total Profit MTD].[Other Expenses*], [Detail Total Profit MTD].Region, [Detail Total Profit MTD].Country, [Detail Total Profit MTD].BAWG
FROM [Detail Total Profit MTD]
ORDER BY 5 DESC;

is sorting properly...

but not:

ORDER BY [Total Revenue]+[Total Expense] DESC

not even:

ORDER BY [NET INCOME] DESC

Whats the difference?

Brianwarnock · May 25, 2015

I didn't understand the Order By you say is working, but one thing I do know is that you cannot sort on aliases, ie use them in an Order By clause.

Brian

nfk · May 25, 2015

I stopped trying to understand what this mutant is when realizing the table name has spaces...

My suggestion, stay out of developing anything other than a cup of coffee on my local starbucks.

Brianwarnock · May 25, 2015

You should give advice , not be rude.

Brian

nfk · May 25, 2015

Brianwarnock said:
You should give advice , not be rude.

Brian

So let me get this straight...

Me suggesting this character to stay away of developing is rude, but he's mindfuck1ng Access SQL extravaganza is not?, I'm sorry but if a junior dev shows anything near this trash I'll have him impaled and let rot in front of the rest of the office... This as a way of showing my appreciation for the rest of the staff...

No one should suffer like I have for having to support some old trailer trash Access mvp.

spikepl · May 25, 2015

NFL you got your wires crossed, mister. Few of the posters here are pro developers. Most do access because they got fed up with excel or because their it-dept screwed them.

Unless you have a useful contribution then shut it.

prabha_friend · May 25, 2015

Hi Brian,
Thanks to your reply. When I order by column number it's doing perfectly but other ways. What is the proper way to sort a calculated field? Can you please modify if possible. I want to understand the problem. Thanks in advance.

nfk · May 25, 2015

prabha_friend said:
Hi Brian,
Thanks to your reply. When I order by column number it's doing perfectly but other ways. What is the proper way to sort a calculated field? Can you please modify if possible. I want to understand the problem. Thanks in advance.

I haven't looked at the query because I can plainly not understand wtf is going on there but I have a hunch that tells me you are trying to use an aggregate function in the order by clause...

You should probably build a subquery to make the whole thing more 'clean'...

Brianwarnock · May 25, 2015

prabha_friend said:
Hi Brian,
Thanks to your reply. When I order by column number it's doing perfectly but other ways. What is the proper way to sort a calculated field? Can you please modify if possible. I want to understand the problem. Thanks in advance.

I sorry to say that you have to quote the whole of the calculation in the Order By clause.
Using stacked or subqueries does not solve the problem, you can create a temporary table and then use a query against that. I would guess that it is to do with the way the compiler handles the sort.

Brian

Edit. The last statement has proved not to be true as illustrated below thanks to VbaInet

vbaInet · May 25, 2015

I can't give you an educated answer on how the engine handles sorting in this particular instance but I can give you pointers. I think that stacking the query will work in this case but you would need to:

1. Change the order of your fields - i.e. the field where the alias is created should come first, then calculations that use that alias can follow. You've done it the other way round. To elaborate, your fields should look like this:

Code:

etc...+[Other Expenses*] AS [Total Expense], 
etc...+[NII Revenues*] AS [Total Revenue], 
[Total Revenue]+[Total Expense] AS [Net Income],

2. Save this query without any sorting and grouping applied, then create a new one and apply your sort.

Brianwarnock · May 26, 2015

I will be interested to know if that works . I don't have Access any more to try it myself , but stacking queries didn't solve the issue way back when.

Brian

prabha_friend · May 26, 2015

Thank you Inet, Brian. @Brian:Stacking Works!

I have a related question:
http://www.access-programmers.co.uk/forums/showthread.php?p=1429693#post1429693

Thanks in advance

The_Doc_Man · May 26, 2015

ORDER BY 5 DESC;

is sorting properly...

but not:

ORDER BY [Total Revenue]+[Total Expense] DESC

not even:

ORDER BY [NET INCOME] DESC

I'm a bit confused here. How does the first example work at all? Is there a syntax element of the ORDER BY clause I am missing? Because unless there is a typo here, the first example sorts by the digit 5 whereas the other two sort by a field or expression. There is no alias name that is a single-character for which this could be a typo, either. The most common typos would be "right finger, wrong row", meaning that the correct field name would have been "R" or "T" - but no field has either name.

Assuming that for some reason this is actually legal, then the 5 is essentially not participating in the sort at all (it's just a constant and not the name of any field or alias in the expression). Which means if the result is in the right order, the underlying tables are already in the right order for what was desired.

pbaldy · May 26, 2015

Doc, it's probably little known and little used, but you can sort by relative position, so that's sorting by the 5th field in the SELECT clause.

pbaldy · May 26, 2015

This mentions it:

http://www.techonthenet.com/sql/order_by.php

I personally have never used it.

vbaInet · May 26, 2015

pbaldy said:
... little used,

... for obvious reasons, e.g. if the field changes position. But there are occasions when it's useful/necessary to use the relative position, an example would be using a SELECT clause in a ADODB recordset to pull data from a file.

Brianwarnock · May 27, 2015

vbaInet said:
I can't give you an educated answer on how the engine handles sorting in this particular instance but I can give you pointers. I think that stacking the query will work in this case but you would need to:

1. Change the order of your fields - i.e. the field where the alias is created should come first, then calculations that use that alias can follow. You've done it the other way round. To elaborate, your fields should look like this:

Code:

etc...+[Other Expenses*] AS [Total Expense], etc...+[NII Revenues*] AS [Total Revenue], [Total Revenue]+[Total Expense] AS [Net Income],

2. Save this query without any sorting and grouping applied, then create a new one and apply your sort.

So this worked.
I now wish I had Access so that I could play around and answer a few vague questions I have about working with aliases.

Have things changed since 2003 ?

Brian

The_Doc_Man · May 27, 2015

Live and learn... I found an explanation of the relative column number ORDER BY clause. Paul - you are right as to its rarity. Since I started using Ac version 2 (before they used year-numbers!), I have never seen that little piece of syntax.

Now that we have established that technically it is legal and has meaning, we still have the question of difference between three methods that superficially look the same.

In the query, field 5 is expressed as "[Total Revenue]+[Total Expense] AS [Net Income]"

So the question is, why does the sort by field 5 work but the sort by the alias or the actual expression not work?

My counter question is, what indexes (if any) are on the two named fields?

vbaInet · May 27, 2015

The_Doc_Man said:
So the question is, why does the sort by field 5 work but the sort by the alias or the actual expression not work?

It's an alias of an alias - the alias does not exist in the table and ORDER BY doesn't recognise that alias as a valid field name. The ORDER BY clause requires fields that exist in the source table/query. As for the relative position, that one is obvious, it's simply sorting based on the position, not by name.

The_Doc_Man · May 27, 2015

I can see that the ALIAS wouldn't work, then. I am guessing that ORDER BY is picky enough that it won't take expressions, only individual fields. Yet column 5 IS an expression. So is this a case where SELECT ..., expression, ... is usable but ORDER BY expression (the same expression) does not, due to limitations in syntax? I looked at several articles using the great Google brain but they weren't entirely helpful on that point and I ran out of time LONG before I ran out of articles.

Whats the difference? (1 Viewer)

Prabhakaran Karuppaih

Retired

Registered User.

Retired

Registered User.

Eledittingent Beliped

Prabhakaran Karuppaih

Registered User.

Retired

AWF VIP

Retired

Prabhakaran Karuppaih

Immoderate Moderator

Wino Moderator

Wino Moderator

AWF VIP

Retired

Immoderate Moderator

AWF VIP

Immoderate Moderator

Similar threads

Users who are viewing this thread