Sort by or statement

  • So I have a query with 3 OR statements:

    SELECT A.ImgName, A.ImgID

    FROM Images A

    INNER JOIN KWImages B

    ON A.ImgID = B.ImgID

    INNER JOIN Keywords C

    ON B.KWID = C.KWID

    INNER JOIN CatImages D

    ON A.ImgID = D.ImgID

    INNER JOIN Categories E

    ON D.CatID = E.CatID

    WHERE

    (C.Keyword LIKE '%Mother''s Day%'OR C.Keyword LIKE '%Mother''s%'OR C.Keyword LIKE '%Day%')

    GROUP BY A.ImgName, A.ImgID


    Is there anyway to write this query where results with the keyword of Mother's Day are first, then Mother's are second, then Day is third?  Basically, I want to sort by the order of my OR statements.  Is this possible?

    TIA,
    Jordon

  • Because of the GROUP BY, you cannot reference the Keyword field directly in your ORDER BY.  This should work.

    SELECT A.ImgName, A.ImgID
     FROM Images A
      INNER JOIN KWImages B
       ON A.ImgID = B.ImgID
      INNER JOIN Keywords C
       ON B.KWID = C.KWID
      INNER JOIN CatImages D
       ON A.ImgID = D.ImgID
      INNER JOIN Categories E
       ON D.CatID = E.CatID
    WHERE (C.Keyword LIKE '%Mother''s%'OR C.Keyword LIKE '%Day%') -- any string that matches '%Mother''s Day%' will match both '%Mother''s%' and '%Day%'
    GROUP BY A.ImgName, A.ImgID
    ORDER BY MIN(CASE WHEN c.Keyword LIKE '%Mother''s Day%' THEN 1 WHEN c.Keyword LIKE '%Mother''s%' THEN 2 WHEN c.Keyword LIKE '%Day%' THEN 3 END)

    I also modified your WHERE clause, because any keyword that matches the pattern for Mother's Day will also match both of the other patterns.  Since LIKE with leading wildcards is not SARGable, we want to have as few as possible.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • drew.allen - Wednesday, February 1, 2017 4:07 PM

    Because of the GROUP BY, you cannot reference the Keyword field directly in your ORDER BY.  This should work.

    SELECT A.ImgName, A.ImgID
     FROM Images A
      INNER JOIN KWImages B
       ON A.ImgID = B.ImgID
      INNER JOIN Keywords C
       ON B.KWID = C.KWID
      INNER JOIN CatImages D
       ON A.ImgID = D.ImgID
      INNER JOIN Categories E
       ON D.CatID = E.CatID
    WHERE (C.Keyword LIKE '%Mother''s%'OR C.Keyword LIKE '%Day%') -- any string that matches '%Mother''s Day%' will match both '%Mother''s%' and '%Day%'
    GROUP BY A.ImgName, A.ImgID
    ORDER BY MIN(CASE WHEN c.Keyword LIKE '%Mother''s Day%' THEN 1 WHEN c.Keyword LIKE '%Mother''s%' THEN 2 WHEN c.Keyword LIKE '%Day%' THEN 3 END)

    I also modified your WHERE clause, because any keyword that matches the pattern for Mother's Day will also match both of the other patterns.  Since LIKE with leading wildcards is not SARGable, we want to have as few as possible.

    Drew

    Thanks so much, Drew!  This worked perfectly.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply