Geofrey Geofrey - 2 months ago 22
SQL Question

Select range based on another column

Given a (sample) table like this:

PERIOD ORD DATA...
Dec16 1
Sep16 3
Aug16 4
Jul16 5
Jun16 7
May16 8
Apr16 9
Mar16 11
Feb16 12
Jan16 13
Dec15 15
Nov15 16
Oct15 17
Sep15 19
Aug15 20
Jul15 21
Jun15 23
May15 24
Apr15 25
Mar15 27
Feb15 28
Jan15 29


where PERIOD is a string, how would I query all rows between given periods e.g. between PERIOD='Mar16' and 'Jun15' (inclusive). Is there any way to leverage the ORD column in the SQL query to get the range of values?

Answer

Aternative to Gordon, this might work depending on your RDBMS

SELECT *
FROM thetable
WHERE ORD BETWEEN (SELECT ORD FROM thetable WHERE PERIOD ='Mar16')
              AND (SELECT ORD FROM thetable WHERE PERIOD ='Jun15')