Question
SQL: Order By ASC mixed with DESC
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
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
AdChoices