CODEWITHSUNDEEP
mysqldatabasebetween
stackoverflow
stackoverflow

Reputation: 19434

In MySQL how do you use BETWEEN with 'AS' columns

Part1. How do I search for values on two different 'AS' columns

Part2. How do I list top 5 nearest values to 98?

Example of Query:

select 
  TITLE.name, 
  (TITLE.value-TITLE.msp) AS Lower, 
  (TITLE.value+TITLE.msp) AS Upper 
FROM TITLE 
WHERE 98 BETWEEN Lower AND Upper;

Upvotes: 0

Views: 62

Answers (1)

Lukas Eder
Lukas Eder

Reputation: 220762

For part 1), you need to use a nested select, as you cannot use renamed projections (SELECT part) as predicates (WHERE part) in the same subselect.

For part 2), use ORDER BY .. LIMIT to get the top 5. Order by the smallest absolute difference to 98

SELECT t.* FROM (
  SELECT
    TITLE.name, 
    (TITLE.value-TITLE.msp) AS Lower, 
    (TITLE.value+TITLE.msp) AS Upper,
    (TITLE.value) AS Value
  FROM TITLE 
) t
WHERE 98 BETWEEN t.Lower AND t.Upper
ORDER BY ABS(98 - t.Value) ASC
LIMIT 5

Upvotes: 3

Related Questions