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