Inner Join with order by and where clase

Question

I have created a stored procedure for filling a drop down. But the order by clause not working on my procedure.

ALTER PROCEDURE proc
    -- Add the parameters for the stored procedure here
    @compID bigint,
    @after datetime
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    CREATE TABLE #tmpAuc ( ProjectID BIGINT, Title VARCHAR(256))
    INSERT INTO #tmpAuc
    SELECT SA.ID ProjectID, SA.Title 
    FROM [dbo].[Sessions] S
        INNER JOIN Auc SA ON S.AucID = SA.ID
    WHERE S.Session < 3 AND SA.Status > 0 AND SA.CompanyID = @companyID AND S.LiveBeginDate > @after
    ORDER BY LiveBeginDate

    SELECT DISTINCT * FROM #tmpAuc
END

I would like to order by descending order of LiveBehinDate

Answer 1

Include the LiveBeginDate in the temp table and from the temp table result ORDER BY LiveBeginDate

CREATE TABLE #tmpAuctions (ProjectID BIGINT, Title VARCHAR(256), LiveBeginDate DATETIME)
INSERT INTO #tmpAuctions (ProjectID, Title, LiveBeginDate)
SELECT SA.ID AS ProjectID, SA.Title, S.LiveBeginDate
FROM [dbo].[Sessions] S
INNER JOIN [Spectrum_Auctions].[dbo].[Auctions] SA ON S.AuctionID = SA.ID
WHERE S.SessionState < 3 AND SA.Status > 0 AND SA.CompanyID = @companyID AND S.LiveBeginDate > @after

SELECT DISTINCT * 
FROM #tmpAuctions
ORDER BY LiveBeginDate

or avoid temp table and directly use the SELECT with JOIN inside the procedure:

SELECT SA.ID AS ProjectID, SA.Title
FROM [dbo].[Sessions] S
INNER JOIN [Spectrum_Auctions].[dbo].[Auctions] SA ON S.AuctionID = SA.ID
WHERE S.SessionState < 3 AND SA.Status > 0 AND SA.CompanyID = @companyID AND S.LiveBeginDate > @after
ORDER BY S.LiveBeginDate