mardi 1 septembre 2015

Check based on columns condition in sql server

How to solve this issue in SQL Server

Table: emp 

Pid  | Address           |  City | datetime                   |  Edate       | level
1    | Homeless          |  Chen | 2014-11-13  09:32:14.000   |2013-02-10    |3
1    | 3913 W. Strong    |  Chen | 2011-03-044 19:04:10.000   |2014-02-04    |7
1    | 1100 W MALLON     |  Chen | 2014-11-13  09:32:14.000   |2013-02-10    |5
2    | 610 W GARLAND #3  |  Hyd  | 2013-11-13  09:32:14.000   |2014-04-02    |4
3    | banvanu           |  chen | 2015-03-044 06:04:10.000   |2015-05-06    |6
3    | naneku            |  chen | 2015-03-044 06:04:10.000   |2015-06-09    |4

based on above table I want output like below

Pid  | Address           |  City | datetime                   |  Edate       | level
1    | 1100 W MALLON     |  Chen | 2014-11-13  09:32:14.000   |2013-02-10    |5
2    | 610 W GARLAND #3  |  Hyd  | 2013-11-13  09:32:14.000   |2014-04-02    |4
3    | naneku            |  chen | 2015-03-044 06:04:10.000   |2015-06-09    |4

we need to get address,city from same table based on below conditions

We have few condition to get output : first level check the max(datetime) based on pid if max(datetime) values same for same pid then same pid need to check max(edate) if we get again same value then we need to check max(level) particilar patient the retrieve address,city for that pid

I tried like below

select * from (select *,row_number()over(partition by id ,order by datetime,edate,level)as rno
               from emp)
where rno=1

but above query not given expect result please tell me how to write query to achive this task in sql server

Aucun commentaire:

Enregistrer un commentaire