CODEWITHSUNDEEP
sqlt-sqlstored-procedures
Dev
Dev

Reputation: 791

Stored Procedure using Group by and Order by

I have this in my stored proc. I would like to Group the records by Offer_Continent and Order by Offer_CountryCode ASC but it doesn't bring the results in the Grouped by continent order.

Below is my select statement

    SELECT
        Offer_SubCatName,
        Offer_TagLine,
        Offer_Type,         
        CustomOffersAttributes.Attribute_Value3,
        Offer_ID,
        Offer_ShortDescription,
        Offer_AccommRating,
        Offer_ResortName,
        Offer_AccommName,
        Offer_CountryCode,
        Offer_Continent,
        Offer_FlightSupplierCode,
        Offer_OriginAirport,
        Offer_DestinationAirport,
        CustomOffersAttributes.Attribute_Value4,
        CustomOffersAttributes.Attribute_Value,
        CustomOffersAttributes.Attribute_Value2,
        Op_Name                     
    FROM
        CustomOffers
        INNER JOIN CustomOffersAttributes ON CustomOffersAttributes.Attribute_ID = dbo.fn_CustomOffers_CheapestDatePrice(CustomOffers.Offer_ID)
        FULL OUTER JOIN Suppliers ON Suppliers.Op_Code = CustomOffers.Offer_FlightSupplierCode
    WHERE
        (
            @CategoryID = 0
        OR
            Offer_CatID = @CategoryID
        )
        AND
        (
            @OfferIDs = ''
            OR
            Offer_ID in (select val from fn_CreateInList(@OfferIDs))
        )
        AND
        (
            @SubCategoryName = ''
        OR
            Offer_SubCatName = @SubCategoryName
        )
        AND ISNULL(Offer_Starts,GETDATE()) <= GETDATE()
        AND ISNULL(Offer_Expires,GETDATE()) >= GETDATE()

    GROUP BY
        Offer_Continent,
        Offer_SubCatName,
        Offer_TagLine,
        Offer_Type,         
        CustomOffersAttributes.Attribute_Value3,            
        Offer_ID,
        Offer_ShortDescription,
        Offer_AccommRating,
        Offer_ResortName,
        Offer_AccommName,
        Offer_CountryCode,
        Offer_Continent,
        Offer_FlightSupplierCode,
        Offer_OriginAirport,
        Offer_DestinationAirport,
        CustomOffersAttributes.Attribute_Value4,
        CustomOffersAttributes.Attribute_Value,
        CustomOffersAttributes.Attribute_Value2,
        Op_Name 
    ORDER BY 
         Offer_Continent ASC, 
         Offer_CountryCode ASC

The above gets me the result as below

Africa (Offer_Continent)

Caribbean(Offer_Continent)

But what I would like to have is a ONLY order by Offer_CountryCode However, when I just order by Offer_CountryCode it looks like below

Caribbean(Offer_Continent)

Afirca (Offer_Continent)

Caribbean(Offer_Continent)

As you can see caribbean is repeated. This is why I thought to use GROUP BY Clause.Anyway what I would like to have is a recors set that looks like below.

Caribbean(Offer_Continent)

Africa (Offer_Continent)

Hope it's clear what I'm trying to do..? Thanks in advance..

Upvotes: 0

Views: 2598

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239664

Okay, so you want to order by continents and then countries, but the order of the continents should be determined by the earliest country within each continent.

I think the following will work:

;WITH Offers AS (
SELECT
    Offer_SubCatName,
    Offer_TagLine,
    Offer_Type,         
    CustomOffersAttributes.Attribute_Value3,
    Offer_ID,
    Offer_ShortDescription,
    Offer_AccommRating,
    Offer_ResortName,
    Offer_AccommName,
    Offer_CountryCode,
    Offer_Continent,
    Offer_FlightSupplierCode,
    Offer_OriginAirport,
    Offer_DestinationAirport,
    CustomOffersAttributes.Attribute_Value4,
    CustomOffersAttributes.Attribute_Value,
    CustomOffersAttributes.Attribute_Value2,
    Op_Name                     
FROM
    CustomOffers
    INNER JOIN CustomOffersAttributes ON CustomOffersAttributes.Attribute_ID = dbo.fn_CustomOffers_CheapestDatePrice(CustomOffers.Offer_ID)
    FULL OUTER JOIN Suppliers ON Suppliers.Op_Code = CustomOffers.Offer_FlightSupplierCode
WHERE
    (
        @CategoryID = 0
    OR
        Offer_CatID = @CategoryID
    )
    AND
    (
        @OfferIDs = ''
        OR
        Offer_ID in (select val from fn_CreateInList(@OfferIDs))
    )
    AND
    (
        @SubCategoryName = ''
    OR
        Offer_SubCatName = @SubCategoryName
    )
    AND ISNULL(Offer_Starts,GETDATE()) <= GETDATE()
    AND ISNULL(Offer_Expires,GETDATE()) >= GETDATE()
)
SELECT
    *
FROM
    Offers o1
ORDER BY
    (SELECT MIN(Offer_Country) from Offers o2 where o2.Offer_Continent = o1.Offer_Continent) asc,
    Offer_Country

Upvotes: 1

Kendrick
Kendrick

Reputation: 3787

You don't need to use group by, since you aren't using any aggregate functions. You can put any fields you want to use to order the data into the order by and just not use group by.

Upvotes: 2

Related Questions