SQL Query get common column with diff values in other columns

Question

I am not very fluent with SQL.. Im just facing a little issue in making the best and efficient sql query. I have a table with a composite key of column A and B as shown below

A	B	C
1	1	4
1	2	5
1	3	3
2	2	4
2	1	5
3	1	4

So what I need is to find rows where column C has both values of 4 and 5 (4 and 5 are just examples) for a particular value of column A. So 4 and 5 are present for two A values (1 and 2). For A value 3, 4 is present but 5 is not, hence we cannot take it.

My explanation is so confusing. I hope you get it.

After this, I need to find only those where B value for 4 (First Number) is less than B value for 5 (Second Number). In this case, for A=1, Row 1 (A-1, B-1,C-4) has B value lesser than Row 2 (A-1, B-2, C-5) So we take this row. For A = 2, Row 1(A-2,B-2,C-4) has B value greater than Row 2 (A-2,B-1,C-5) hence we cannot take it.

I Hope someone gets it and helps. Thanks.

Answer 1

Rows containing both c=4 and c=5 for a given a and ordered by b and by c the same way.

select a, b, c
from (
    select tbl.*, 
       count(*) over(partition by a) cnt,
       row_number() over (partition by a order by b) brn,
       row_number() over (partition by a order by c) crn
    from tbl
    where c in (4, 5)
) t
where cnt = 2 and brn = crn;

EDIT

If an order if parameters matters, the position of the parameter must be set explicitly. Comparing b ordering to explicit parameter position

with params(val, pos) as (
    select 4,2 union all
    select 5,1
)
select a, b, c
from (
    select tbl.*, 
       count(*) over(partition by a) cnt,
       row_number() over (partition by a order by b) brn,
       p.pos
    from tbl 
    join params p on tbl.c = p.val
) t
where cnt = (select count(*) from params) and brn = pos;

Answer 2

I assume you want the values of a where this is true. If so, you can use aggregation:

select a
from t
where c in (4, 5)
group by a
having count(distinct c) = 2;