Priority to Order by instead of Where

Question

Priority to Order by instead of Where

Navigation

#1 by (3 votes)

1

Good morning to all, this question has the purpose of finding how I can generate an indicator of existence when the record is repeated: because the application has been previously generated, then I show the related query:

SELECT id AS Existe
FROM baja_products 
WHERE 
    idGridCheck = '65860'   
    AND Aprob_ResG_Abast = 'True'  
    AND Aprob_ResG_Finan = 'True'
    AND Aprob_ResG_Gnral = 'True'
    AND Aprob_ResG_Legal = 'True'
ORDER BY 1 DESC;

Now the table in question already has two records pq previously approved the process for that item and presents the following scenario:

When it goes more than one row, the conditional does not give me the existence indicator correctly, so it skips the validation immersed in the Where.

The question is related to what I can do to give priority to order by or how can I put my where to find the match if there is a more recent record always take the last mentioned?

I hope you can help me, greetings and many thanks to all.

sql-server tsql

asked by Jose Reynel Chaux Perez 26.04.2018 в 18:56

source

1 answer

Create Stored Procedure to fill a table Program that does not end

score 3 · Accepted Answer

One way to solve it would be, if we first get the highest id for each idGridCheck using a GROUP BY and a MAX , then we would simply have to do a JOIN on this subquery, something like that :

SELECT  BP.*
    FROM baja_products BP
    INNER JOIN ( SELECT     idGridCheck, MAX(id) id
                FROM baja_products 
                WHERE 
                    prob_ResG_Abast = 'True'  
                    AND Aprob_ResG_Finan = 'True'
                    AND Aprob_ResG_Gnral = 'True'
                    AND Aprob_ResG_Legal = 'True'
                GROUP BY idGridCheck
        ) M
        ON M.idGridCheck = BP.idGridCheck
        AND M.id = BP.id
    WHERE 
        BP.idGridCheck = '65860'   
        AND BP.Aprob_ResG_Abast = 'True'  
        AND BP.Aprob_ResG_Finan = 'True'
        AND BP.Aprob_ResG_Gnral = 'True'
        AND BP.Aprob_ResG_Legal = 'True'

The subquery will remove the idGridCheck = '65860' filter if you wish to eventually expand the idGridCheck spectrum, but you can also add it to be more performant if you only need to consult for a single idGridCheck .