Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
yohan_w
Starting Member
2 Posts |
Posted - 2013-01-11 : 02:19:29
|
hi,I'm having trouble with max functionshere are the situationsI have table like below ---------------------------------------| ID | Salary | Date |---------------------------------------| 1001 | 300 | 2012-01-01 |---------------------------------------| 1001 | 350 | 2012-12-01 |---------------------------------------Now I have to get the salary from ID 1001which max(date) <= 2013-01-01I tried this:select distinct ID, Salary from Salary_Table where ID='1001group by ID, Salarhaving Cast(convert(varchar(8),max([Date]), 112) as datetime)<='20130101'the correct result I want is1001 350 but it gives me 1001 301001 350please help methx u,... |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 02:59:38
|
[code]SELECT ID,Salary,[Date]FROM(SELECT ROW_NUMBER() OVER (PARTITION BY ID ORDER BY [Date] DESC) AS Seq,*FROM tableWHERE [date] <='20130101')tWHERE Seq=1[/code]------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
yohan_w
Starting Member
2 Posts |
Posted - 2013-01-11 : 03:20:32
|
Thank u for your quick reply!Your code works like a charm and SOLVED my problem :)thanks! |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-11 : 03:21:57
|
welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|