SQLTeam.com | Weblogs | Forums

Query with condition


#1

Hello SQL expert,

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


#2

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?

What have you tried so far?


#3

Hi djj55, you are correct...they are more columns but I only displayed those to simplify it. Let's called those column3, Column4, Column5 and Column6.


#4

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?


#5

Hi bitsmed,

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.

Thanks bitsmed


#6

Would I be right if I state:

  • Each studentid must have at least one A and one B state
  • If there is a V state, show it (only one) otherwise show any of the A or B states (only one)
  • It doesn't matter in which order the records are

#7

Hi bitsmed,

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


#8

See if this does the trick for you:

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
;

#9

Deleted this response


#10

Hello bitsmed,

Let me screen shots the table and the records:

and here is the result set..we are missing the studentID of:
356488, 325465.

So, the query only seek the V but when there is no V, this query does not include it on the result set


#11

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.


#12

Great, that works...thanks again bitsmed. You are a SUPER SQL GENIUS!