CODEWITHSUNDEEP
sqlwhere-clausebetween
Drishti
Drishti

Reputation: 11

Optimizing SQL query's where clause

I have a SQL query which has a where condition

WHERE
  quarter IN ('2015 Q1', '2014 Q4', '2015 Q2', '2015 Q3', '2015 Q4',
              '2016 Q1', '2016 Q2', '2016 Q3', '2016 Q4', '2017 Q1',
              '2017 Q2', '2017 Q3')

Every quarter I hardcode the last Quarter. Is there an optimized way of writing it?

Upvotes: 1

Views: 54

Answers (5)

Akjun
Akjun

Reputation: 370

This should do the trick:

 where quarter between '2014 Q4' and '2017 Q3';

You could change the between condition to see the result you want.

Upvotes: 0

Ben
Ben

Reputation: 2209

You could give every row an auto-incrementing* ID, so you could write where ID > 0

Using IDs is the standard way of doing this, although, not the only way.

If you have other stuff in this table, don't auto-increment, although you should have just quarters.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269933

I think this does what you want:

WHERE quarter >= '2014 Q4'

This assumes that you don't have future quarters or other strings. If you have other strings, then:

WHERE quarter >= '2014 Q4' AND quarter LIKE '____ Q_'

Upvotes: 0

Noel Lopes
Noel Lopes

Reputation: 106

I think this should work (not sure -- it should be -- is faster):

WHERE
  quarter >= '2014 Q4' and quarter <= '2017 Q3'

however, ideally, you table should have two fields: year (int), quarter (int: 1,2,3,4)

Upvotes: 1

Stephan Lechner
Stephan Lechner

Reputation: 35154

Create a table in which you store the (valid) quarters, e.g.

create table quarters (
   quarter varchar2(20) primary key
);

Then insert the valid quarters in there:

insert into quarters(quarter) values ('2015 Q1');
...
insert into quarters(quarter) values ('2017 Q3');

such that you then can write:

select *
from myTable m join quarter q on m.quarter = q.quarter

Thereby, you do not have to alter the query every time but just to insert a new tuple in the quarters-table.

Upvotes: 0

Related Questions