2 Replies Latest reply on May 10, 2007 9:36 AM by Newsgroup_User

    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
        • 1. Re: SQL: Order By ASC mixed with DESC
          sanaullah
          Hi
          try like this

          ORDER BY BudgetYear DESC, BUDGETSHORTDESCRIPTION asc

          Thanks
          • 2. Re: SQL: Order By ASC mixed with DESC
            Level 7
            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
            >
            >