CODEWITHSUNDEEP
sqlsql-serverdatabasecountwhere-clause
TheLegend28
TheLegend28

Reputation: 43

SQL - How to add count in where clause condition

My current query has information as below : 

| employee_no | employee_name | employee_dept | company_id |
------------------------------------------------------------
|    P101     |     John      |      C1       |    comp1   |
|    P101     |     John      |      C1       |    comp2   |
|    P102     |     Mary      |      C2       |    comp1   |

This is a simplified version of the query result. Currently, the result I want is as below : 

| employee_no | employee_name | employee_dept | company_id |
------------------------------------------------------------
|    P101     |     John      |      C1       |    comp2   |
|    P102     |     Mary      |      C2       |    comp1   |

I want the query to check if there are two records of the same employee, the query should take the comp2 record.

Upvotes: 2

Views: 73

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81930

The following will select the last row ordered by company_id desc However, this would be alpha sort and NOT necessarily proper sequence. So, if there is an identity or an effective date, use that instead of Order By company_id

Example

Select top 1 with ties *
 From  YourTable
 Order By Row_Number() over (Partition By employee_no Order By company_id desc)

Upvotes: 3

Related Questions