July 12, 2010 at 5:14 am
hello, friends i have
table: EmpMst
id empcod empname Sdate status
1 e1 abc 13/02/2010 1
2 e1 abc 12/01/2010 1
3 e2 XY 13/01/2010 1
i need record with id 1 and 3 only how do i get in sql server 2005.(means distinct empcod only).
Keyur Gohel
July 12, 2010 at 5:18 am
Hi,
I understood that u want to display rows of id 1 and 3 in ssrs 2005.. right?
if so use filter in table/matrix...
Regards,
Gayathri 🙂
July 12, 2010 at 6:43 am
KeyurGohel (7/12/2010)
hello, friends i havetable: EmpMst
id empcod empname Sdate status
1 e1 abc 13/02/2010 1
2 e1 abc 12/01/2010 1
3 e2 XY 13/01/2010 1
i need record with id 1 and 3 only how do i get in sql server 2005.(means distinct empcod only).
It kind of depends on how you want to define the destinction. Let's say that you only want the latest date column. Then you could do something like this:
SELECT x.*
FROM (SELECT *, row_number() over (order by Sdate desc) as rownum
from EmpMst) as x
WHERE x.rownum = 1
Other options include using TOP or MAX with a sub-select to achieve the same type of behavior.
But that might not meet the business need. So the question is, how do you define the unique value?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 20, 2010 at 3:17 pm
You can get the requested resultset in many ways.. here is one simple way...
Select Distinct TBL_1.EmpID, TBL_1.Name, TBL_2.UpdateTime from EmpMst TBL_1 With (Nolock)
Left Outer Join EmpMst TBL_2 With (Nolock) On TBL_1.EmpID=TBL_2=EmpID And UpdateTime=(Select Max(UpdateTime) from EmpMst With (Nolock) Where EmpID=TBL_1)
SKC.
July 21, 2010 at 6:08 am
sk-458789 (7/20/2010)
You can get the requested resultset in many ways.. here is one simple way...Select Distinct TBL_1.EmpID, TBL_1.Name, TBL_2.UpdateTime from EmpMst TBL_1 With (Nolock)
Left Outer Join EmpMst TBL_2 With (Nolock) On TBL_1.EmpID=TBL_2=EmpID And UpdateTime=(Select Max(UpdateTime) from EmpMst With (Nolock) Where EmpID=TBL_1)
SKC.
Why all the NOLOCK hints?
That can lead to seriously bad data being returned by the query.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply