CODEWITHSUNDEEP
sqlnetezza
geofrey
geofrey

Reputation: 466

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?

Upvotes: 1

Views: 555

Answers (2)

Thomas G
Thomas G

Reputation: 10216

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')

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270463

You would convert the period to a date and use that for comparison. For instance, some databases support to_date() and it would look like:

where to_date(period, 'MonYY') between to_date('Jun15', 'MonYY') and to_date('Mar16', 'MonYY')

Note that the specific function for converting your period to a date differs by database, which is not (yet?) specified on the question. But this gives you an idea of how to do it.

And, if you are going to store the periods as string, you should use YYYY-MM formats. This sorts correctly and allows you to use inequality comparisons.

Upvotes: 0

Related Questions