Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

SQL sorting problem

Discussion in 'Information Technology' started by Adam@nospam.com, Jul 28, 2006.

  1. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    Hi All,

    I've finally managed to create a great crosstab query from a union
    query, but I want to sort a field in there in a custom way

    I have records under the 'Category' field called "I/C Presented","I/C
    Answered","PCA 20", "PCA 40".

    Now I want to sort the records in the way I've written them above,
    however if I choose to sort the records by Category in Ascending order
    "I/C Answered" appears at the top.

    Below is a copy of the SQL used in my crosstab query:

    ----------------
    TRANSFORM Max(qryUnionTest.Measurement) AS MaxOfMeasurement
    SELECT qryUnionTest.Teams, qryUnionTest.Category,
    Max(qryUnionTest.Measurement) AS [Total Of Measurement]
    FROM qryUnionTest
    GROUP BY qryUnionTest.Teams, qryUnionTest.Category
    ORDER BY qryUnionTest.Teams, qryUnionTest.Category
    PIVOT Format([MeasurementDate],"mmm") In
    ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
    ----------------

    Now under ORDER BY can I add:

    ORDER By qryUnionTest.Category In ("I/C Presented","I/C Answered","PCA
    20", "PCA 40") ?

    Many Thanks

    Adam
     
  2. Loading...

    Similar Threads Forum Date
    Microsoft Brings 64-Bit Perks in SQL Server 2000 Service Pack (Ziff Davis) Interfaith Dialogues May 7, 2005

  3. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On 13 Jun 2006 09:32:43 -0700, "Adam@nospam.com"
    <adamevans81@gmail.com> wrote:

    >Now under ORDER BY can I add:
    >
    >ORDER By qryUnionTest.Category In ("I/C Presented","I/C Answered","PCA
    >20", "PCA 40") ?


    No; but you can use

    ORDER BY Switch([Category] = "I/C Presented", 1,
    [Category] = "I/C Answered", 2,
    [Category] = "PCA 20", 3,
    [Category] = "PCA 40, 4,
    True, 5)


    John W. Vinson[MVP]
     
  4. Adam@nospam.com

    Adam@nospam.com
    Expand Collapse
    Guest

    Brilliant, thank you.

    John Vinson wrote:

    > On 13 Jun 2006 09:32:43 -0700, "Adam@nospam.com"
    > <adamevans81@gmail.com> wrote:
    >
    > >Now under ORDER BY can I add:
    > >
    > >ORDER By qryUnionTest.Category In ("I/C Presented","I/C Answered","PCA
    > >20", "PCA 40") ?

    >
    > No; but you can use
    >
    > ORDER BY Switch([Category] = "I/C Presented", 1,
    > [Category] = "I/C Answered", 2,
    > [Category] = "PCA 20", 3,
    > [Category] = "PCA 40, 4,
    > True, 5)
    >
    >
    > John W. Vinson[MVP]
     

Share This Page