Here are the Student table with the values and I want to query to get just one Student ID record for status A and S only if there is no V value on Status. However, if the value V
listed in between values A and S, then we will need to consider V record only.
So for example: StudentID 325465 has A and S, we just need one record on either A or S, it does not matter but on the StudentID 123456 and 224568, because V values is in between
the values of A and S, then we will consider the record with V status only.
How do I write the query to get this logic?
StudentID Status
123456 A
123456 A
123456 A
123456 V
123456 S
123456 S
123456 A
356488 A
325465 A
325465 S
224568 A
224568 A
224568 A
224568 A
224568 A
224568 V
224568 A
224568 A
224568 A
224568 S
224568 S
First let me say that SQL Server does not save in a definite order, so your between may not be if you only have the two columns. So, does your table have other columns?
As @djj55 indicated we need to know, how to order your records. Also studentid 224568 with status V is between status A and A (and you say in your rules, it must be between A and S) so I assume this studentid should not be shown.
Bottom line - we need more information, preferably:
Which database engine and version are you using
Explain how you determine the order of the records
Table definition (as create statement)
Sample data (as insert statement)
Expected output (from your supplied sample data)
Your query (what have you tried so far)
With these informations, we are much better equiped to guide you to a solution.
Ps.: This wouldn't happen to be a school assignment?
Sorry for the confusion. The rules are actually can be in between A and S, just A or just S. So, if the value between A and V, we need to get the record with V only. If the value between S and V, we need the records with V value only. If the value between A and S, we take either one of them. If the value only shows A only, we take one record of it as well as if the value shows only S. If the value is just V only, we dont need it.
I am using SQL 2014. I am currently seeking how to write the query based on this logic and so I dont jave one written yet. I am not sure if the order is important or not but the goal is to get only one record on either of above condition.
No, this is not for the school assigment, this is just for my testing purposes with Sql statement. I am learning with sql statements.
Here are the answer for your statement:
Each studentid must have at least one A and one B state
answer: Each student only allow to have one, either A or S status.
If there is a V state, show it (only one) otherwise show any of the A or B states (only one)
answer: correct but instead B, is a S status
It doesn't matter in which order the records are
answer: correct
with cte1(studentid)
as (select studentid
from student
where status in ('A','S')
group by studentid
having min(status)<>max(status)
)
,cte2(studentid,status,rn)
as (select b.studentid
,b.status
,row_number(partition by a.studentid
order by case when b.status='V' then 0 else 1 end
)
as rn
from cte as a
inner join student as b
on b.studentid=a.studentid
)
select studentid
,status
from cte2
where rn=1
;
First of all, I had a syntax error in the row_number section (but I see you already corrected that).
Secondly the query expects a student to have status A and status B (which is what I did understand, was the rule).
Studentid 356488 and 325465 "only" have status A. If the rule is: student must have status A or status b, then remove the having line.
