SQL: Order By ASC mixed with DESC

[francois-yanick]

Hi everyone,

I guest this is a very advanced question concerning ORDER BY with an SQL query as I intend to have 2 type of order from the same table.

Here it is: I have a table with 2 fiels:

field 1 = BudgetShort Description which is a varchar and it is unique
field 2 = BudgetAmountAvailable wich is a float

the field BudgetShortDescription is contain a structure like this:

[year].[program area].[area].[project].[activity].[action].[task]

e.g.: 2007.6.1.2.4.5.2

it is long story to explain how this table is generated but, in this issue, I need to use it where I would like to order by year DESC (so 2007 will come first and than 2006, 2005, etc...). But, I also want to order by the budget's structure (after the year) in an ascendant way so IU can have 2007.6.1.2.4.5.2, 2007.6.1.2.4.5.3, 2007.6.1.2.4.5.4, etc...

I thought about creating a first query (the one that take the information from the table in the database) where I created a new variable in my query:

SELECT distinct dbo.ContractBudgetDistribution.budgetId, dbo.BudgetList.BudgetShortDescription AS BudgetShortDescription, LEFT(dbo.BudgetList.BudgetShortDescription, 4) AS BudgetYear
FROM dbo.ContractBudgetDistribution INNER JOIN dbo.BudgetList ON dbo.ContractBudgetDistribution.BudgetId = dbo.BudgetList.BudgetID
ORDER BY BudgetYear DESC

This work okay. But, after this, my idea was to go with a query of a query, just like this:

SELECT *
FROM qGetBudgetList
ORDER BY BUDGETSHORTDESCRIPTION

But it is not doing what I want. Of course because the system does not understand how to split such a same field. The solution to add a new field in the table for the year still an option but the problem will still the same as well as I still (a lot of still here) need to order in two ways what I got.

Any help will be appreciated here!

Thank's

[sanaullah]

Hi
try like this

ORDER BY BudgetYear DESC, BUDGETSHORTDESCRIPTION asc

Thanks

[Newsgroup_User]

Try this instead:

SELECT DISTINCT cbd.budgetId, bl.BudgetShortDescription
FROM dbo.ContractBudgetDistribution cbd
INNER JOIN dbo.BudgetList bl ON cbd.BudgetId = bl.BudgetID
ORDER BY LEFT(bl.BudgetShortDescription, 4) DESC, bl.BudgetShortDescription

HTH,
Carl

francois-yanick wrote:
> Hi everyone,
>
> I guest this is a very advanced question concerning ORDER BY with an SQL query
> as I intend to have 2 type of order from the same table.
>
> Here it is: I have a table with 2 fiels:
>
> field 1 = BudgetShort Description which is a varchar and it is unique
> field 2 = BudgetAmountAvailable wich is a float
>
> the field BudgetShortDescription is contain a structure like this:
>
> [year].[program area].[area].[project].[activity].[action].[task]
>
> e.g.: 2007.6.1.2.4.5.2
>
> it is long story to explain how this table is generated but, in this issue, I
> need to use it where I would like to order by year DESC (so 2007 will come
> first and than 2006, 2005, etc...). But, I also want to order by the budget's
> structure (after the year) in an ascendant way so IU can have 2007.6.1.2.4.5.2,
> 2007.6.1.2.4.5.3, 2007.6.1.2.4.5.4, etc...
>
> I thought about creating a first query (the one that take the information from
> the table in the database) where I created a new variable in my query:
>
> SELECT distinct dbo.ContractBudgetDistribution.budgetId,
> dbo.BudgetList.BudgetShortDescription AS BudgetShortDescription,
> LEFT(dbo.BudgetList.BudgetShortDescription, 4) AS BudgetYear
> FROM dbo.ContractBudgetDistribution INNER JOIN dbo.BudgetList ON
> dbo.ContractBudgetDistribution.BudgetId = dbo.BudgetList.BudgetID
> ORDER BY BudgetYear DESC
>
> This work okay. But, after this, my idea was to go with a query of a query,
> just like this:
>
> SELECT *
> FROM qGetBudgetList
> ORDER BY BUDGETSHORTDESCRIPTION
>
> But it is not doing what I want. Of course because the system does not
> understand how to split such a same field. The solution to add a new field in
> the table for the year still an option but the problem will still the same as
> well as I still (a lot of still here) need to order in two ways what I got.
>
> Any help will be appreciated here!
>
> Thank's
>
>