| Groups | Blog | Home |
sql server new users : Order by query question
Ok, this is stupid, I know I should know this but...
I've got the following SELECT ID, Name + ': ' + SerialNo AS Name FROM Products ORDER BY Name This always orders by the Products.Name field. I notice that if I change the AS Name to something that isn't in the Products table and change the Order by accordingly, it will then sort on that created field. My question is, given that there are cases where I can't just use a unique name for the concatenated field, how do I sort on the concatentated field explicitly instead of on the Products.Name? Thanks! James Hancock
Maybe the syntax looks not that flashy but it works:
SELECT ID, Name + ': ' + SerialNo FROM Products ORDER BY Name + ': ' + SerialNo [quoted text, click to view] "James Hancock" wrote:
> Ok, this is stupid, I know I should know this but... > > I've got the following > > SELECT ID, Name + ': ' + SerialNo AS Name FROM Products ORDER BY Name > > This always orders by the Products.Name field. I notice that if I change > the AS Name to something that isn't in the Products table and change the > Order by accordingly, it will then sort on that created field. > > My question is, given that there are cases where I can't just use a unique > name for the concatenated field, how do I sort on the concatentated field > explicitly instead of on the Products.Name? > > Thanks! > James Hancock > >
Ugh.... that's nasty syntax to use... Makes it really hard to maintain the
query because if you make a change to the select you also have to make a change to the order by... But hey, it works... anyone have a better way? I can't believe that they left this out... James Hancock [quoted text, click to view] "Geert Vanhove" <GeertVanhove@discussions.microsoft.com> wrote in message news:ABD579C4-6DAA-4288-938A-ECAC56B4315D@microsoft.com... > Maybe the syntax looks not that flashy but it works: > SELECT ID, Name + ': ' + SerialNo FROM Products > ORDER BY Name + ': ' + SerialNo > > > > "James Hancock" wrote: > >> Ok, this is stupid, I know I should know this but... >> >> I've got the following >> >> SELECT ID, Name + ': ' + SerialNo AS Name FROM Products ORDER BY Name >> >> This always orders by the Products.Name field. I notice that if I change >> the AS Name to something that isn't in the Products table and change the >> Order by accordingly, it will then sort on that created field. >> >> My question is, given that there are cases where I can't just use a >> unique >> name for the concatenated field, how do I sort on the concatentated field >> explicitly instead of on the Products.Name? >> >> Thanks! >> James Hancock >> >> >>
Derived table.
select * from ( select id as id , name + ':' + SerialNo as sort_key from products ) d_t order by d_t.sort_key ; -- Walter Clayton Any technology distinguishable from magic is insufficiently advanced. [quoted text, click to view] "James Hancock" <~jamie@darwinconsulting.com> wrote in message
news:%233%231urLEFHA.2572@tk2msftngp13.phx.gbl... > Ugh.... that's nasty syntax to use... Makes it really hard to maintain > the query because if you make a change to the select you also have to make > a change to the order by... > > But hey, it works... anyone have a better way? I can't believe that they > left this out... > > James Hancock > > "Geert Vanhove" <GeertVanhove@discussions.microsoft.com> wrote in message > news:ABD579C4-6DAA-4288-938A-ECAC56B4315D@microsoft.com... >> Maybe the syntax looks not that flashy but it works: >> SELECT ID, Name + ': ' + SerialNo FROM Products >> ORDER BY Name + ': ' + SerialNo >> >> >> >> "James Hancock" wrote: >> >>> Ok, this is stupid, I know I should know this but... >>> >>> I've got the following >>> >>> SELECT ID, Name + ': ' + SerialNo AS Name FROM Products ORDER BY Name >>> >>> This always orders by the Products.Name field. I notice that if I >>> change >>> the AS Name to something that isn't in the Products table and change the >>> Order by accordingly, it will then sort on that created field. >>> >>> My question is, given that there are cases where I can't just use a >>> unique >>> name for the concatenated field, how do I sort on the concatentated >>> field >>> explicitly instead of on the Products.Name? >>> >>> Thanks! >>> James Hancock >>> >>> >>> > >
James,
The simplest way to get this working is just use the column number: Order 2 That means you sort on the second column on your selected column list. That always works better when you have a complicated column list as using case statement. Cheers, Roger [quoted text, click to view] "James Hancock" wrote:
> Ugh.... that's nasty syntax to use... Makes it really hard to maintain the > query because if you make a change to the select you also have to make a > change to the order by... > > But hey, it works... anyone have a better way? I can't believe that they > left this out... > > James Hancock > > "Geert Vanhove" <GeertVanhove@discussions.microsoft.com> wrote in message > news:ABD579C4-6DAA-4288-938A-ECAC56B4315D@microsoft.com... > > Maybe the syntax looks not that flashy but it works: > > SELECT ID, Name + ': ' + SerialNo FROM Products > > ORDER BY Name + ': ' + SerialNo > > > > > > > > "James Hancock" wrote: > > > >> Ok, this is stupid, I know I should know this but... > >> > >> I've got the following > >> > >> SELECT ID, Name + ': ' + SerialNo AS Name FROM Products ORDER BY Name > >> > >> This always orders by the Products.Name field. I notice that if I change > >> the AS Name to something that isn't in the Products table and change the > >> Order by accordingly, it will then sort on that created field. > >> > >> My question is, given that there are cases where I can't just use a > >> unique > >> name for the concatenated field, how do I sort on the concatentated field > >> explicitly instead of on the Products.Name? > >> > >> Thanks! > >> James Hancock > >> > >> > >> > >
Thanks! That works nicely!
They really should have some sort of syntax for this... [quoted text, click to view] "DQ" <DQ@discussions.microsoft.com> wrote in message news:DCDB647A-8EC0-4CC0-8A92-36EC17660391@microsoft.com... > James, > > The simplest way to get this working is just use the column number: > > Order 2 > > That means you sort on the second column on your selected column list. > That > always works better when you have a complicated column list as using case > statement. > > Cheers, > > Roger > > "James Hancock" wrote: > >> Ugh.... that's nasty syntax to use... Makes it really hard to maintain >> the >> query because if you make a change to the select you also have to make a >> change to the order by... >> >> But hey, it works... anyone have a better way? I can't believe that they >> left this out... >> >> James Hancock >> >> "Geert Vanhove" <GeertVanhove@discussions.microsoft.com> wrote in message >> news:ABD579C4-6DAA-4288-938A-ECAC56B4315D@microsoft.com... >> > Maybe the syntax looks not that flashy but it works: >> > SELECT ID, Name + ': ' + SerialNo FROM Products >> > ORDER BY Name + ': ' + SerialNo >> > >> > >> > >> > "James Hancock" wrote: >> > >> >> Ok, this is stupid, I know I should know this but... >> >> >> >> I've got the following >> >> >> >> SELECT ID, Name + ': ' + SerialNo AS Name FROM Products ORDER BY Name >> >> >> >> This always orders by the Products.Name field. I notice that if I >> >> change >> >> the AS Name to something that isn't in the Products table and change >> >> the >> >> Order by accordingly, it will then sort on that created field. >> >> >> >> My question is, given that there are cases where I can't just use a >> >> unique >> >> name for the concatenated field, how do I sort on the concatentated >> >> field >> >> explicitly instead of on the Products.Name? >> >> >> >> Thanks! >> >> James Hancock >> >> >> >> >> >> >> >> >>
Don't see what you're looking for? Try a search.
|
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |