AND criteria in where clause [message #658244] |
Wed, 07 December 2016 08:40 |
|
blyzz
Messages: 10 Registered: October 2015
|
Junior Member |
|
|
I have a table for students with code and description in 2 columns
name code des
test1 A2 High Grade
test1 B1 Avg Math Grade
test2 B3 Avg Math Grade
I want to specify the "and" criteria in where clause to get students who got grades betwen A1-A4 and B1-B4 grade
select * from tbl where code between A1-A4 and code between B1-B4
I am getting No value but it should return 2 records for test1
but if I use OR I am getting both the record.
The issue is I want to select the values when both the conditions are met.
How can I do that?
Thanks,
blyzz
|
|
|
|
|
|
Re: AND criteria in where clause [message #658385 is a reply to message #658260] |
Mon, 12 December 2016 06:54 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
You can also do a self join
select a.name
from my_table b,my_table a
where a.name = b.name
and a.code between 'A1' AND 'A4'
AND B.CODE BETWEEN 'B1' AND 'B4'
GROUP BY a.name;
|
|
|
Re: AND criteria in where clause [message #658387 is a reply to message #658385] |
Mon, 12 December 2016 07:20 |
Solomon Yakobson
Messages: 3286 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Self join (or intersect as it was suggested earlier) will work but it inefficient. OP should use analytics. Something like:
with t as (
select name,
sum(
distinct case
when code between 'A1' AND 'A4' then 1
when code between 'B1' AND 'B4' then 2
else 4
end
) over(partition by name) flag,
row_number() over(partition by name order by 1) rn
from my_table
)
select name
from t
where flag = 3
and rn = 1
/
SY.
[Updated on: Mon, 12 December 2016 07:23] Report message to a moderator
|
|
|