CODEWITHSUNDEEP
sqlsql-serverjoininner-joinsql-query-store
JohnMathew
JohnMathew

Reputation: 528

Inner Join with order by and where clase

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

Upvotes: 2

Views: 253

Answers (2)

Arulkumar
Arulkumar

Reputation: 13237

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

Upvotes: 2

Roger
Roger

Reputation: 261

That’s because your data is ordered when writing to the temp table that resides in temp dB. Reading from temp dB is never guaranteed to be in order. So when you select star from your temp table that’s what you get. Get rid of the temp table and do the select directly. This will be faster and more efficient as well. If your proc becomes more complex, use CTEs instead of temp tables as they are easier to conceptualize and run much faster in all cases.

Upvotes: 1

Related Questions