February 1, 2017 at 3:40 pm
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
February 1, 2017 at 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
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 1, 2017 at 7:15 pm
drew.allen - Wednesday, February 1, 2017 4:07 PMBecause 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