June 17, 2009 at 4:46 am
I have a table employees with fields empno (PK), name, office. Another table postings with fields ser, empno, office, startdate contain posting detail of each employee. Empno is going in postings table as foreign key. An employee may have many posting records in posting table. I have to get data in following form
empno, name, office, startdate
(start date will be max start date for that employee) . So only one record will be retrieved for each employee with startdate as maximum startdate in his postings table. Is there any way for this task without use of cursor.
Regards
DBDigger Microsoft Data Platform Consultancy.
June 17, 2009 at 4:53 am
Have a look at the following on BOL.
GROUP BY AND MAX
and then the left join or inner depending on if you want to show empno with no postiings
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 17, 2009 at 4:53 am
select emp.empno, emp.name, posting.office, posting.startdate
from employee emp Inner Join Posting
on emp.empno=posting.empno
Pls post the table structure with sample data for better answers.
June 17, 2009 at 4:56 am
Yeah. And as rightly pointed by Christopher, use GROUP BY and MAX...
June 17, 2009 at 4:57 am
ps (6/17/2009)
select emp.empno, emp.name, posting.office, posting.startdatefrom employee emp Inner Join Posting
on emp.empno=posting.empno
this query will simply return all start dates for an employee. And there will be records for each start date in postings table. My requirement is to get just max(startdate) for each employee. Hence there will be just one record for each employee.
DBDigger Microsoft Data Platform Consultancy.
June 17, 2009 at 5:04 am
Have you looked in BOL yet?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 17, 2009 at 5:11 am
create table employee
(empno int,
name varchar(100),
Office varchar(100))
create table Posting
(
ser int,
empno int,
office varchar(100),
startdate datetime
)
Insert into employee
select 1,'pradeep','mumbai'
union
select 2,'kumar','pune'
union
select 3, 'singh','bangalore'
insert into posting
select 1,1,'delhi', getdate()-200
union
select 2,1,'noida',getdate()-100
union
select 3,1,'mumbai',getdate()-10
union
select 4,2,'pune', getdate()-2
union
select 5,2,'chennai',getdate()-100
union
select 6,2,'bangalore',getdate()-150
select emp.empno, emp.name, posting.office, posting.startdate
from employee emp Inner Join Posting
on emp.empno=posting.empno
where posting.ser in (select max(ser) from posting group by empno)
June 17, 2009 at 5:15 am
forgot to paste the results.
OUTPUT:-
---------
empno name office startdate
----------- --------- ----------- -----------------------
1 pradeep mumbai 2009-06-07 16:37:49.500
2 kumar bangalore 2009-01-18 16:37:49.500
(2 row(s) affected)
Is this what you need?
June 17, 2009 at 5:15 am
ps (6/17/2009)
select emp.empno, emp.name, posting.office, posting.startdatefrom employee emp Inner Join Posting
on emp.empno=posting.empno
where posting.ser in (select max(ser) from posting group by empno)
Thanks a lot PS for this help. It is my required output.
Thanks and Regards
DBDigger Microsoft Data Platform Consultancy.
June 17, 2009 at 5:17 am
Glad I could help 🙂
June 17, 2009 at 5:18 am
Why is there an Office field in both tables?
Did you want the office from the employee or from the posting returned?
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 17, 2009 at 5:21 am
Christopher Stobbs (6/17/2009)
Why is there an Office field in both tables?Did you want the office from the employee or from the posting returned?
I was confused too!! but didnt ask:w00t:. That's redundant as the entire history of office is maintened in the posting table.
June 17, 2009 at 5:24 am
Not sure that it is redundant because if he wants to know the office where the actually employee comes from rather than the office where the post was made, then it change everything.
It would mean you could do a straight GROUP BY Max with out the in clause which change the performance of the query somewhat...
but like you said the OP did not clarify this so it's hard to know
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
June 17, 2009 at 5:27 am
My other concern is that using the MAX on the "Identity" of the postings table is prob not a good idea as this does not mean 100% that you gonna get the MAX date along with it.
If some records are deleted and re added etc, the order of the PK will not reflect the order of the Date.
Sorry I'm not trying to be funny or Arsy but just want the OP to understand the query that they going to be using.
----------------------------------------------
Try to learn something about everything and everything about something. - Thomas Henry Huxley
:w00t:
Posting Best Practices[/url]
Numbers / Tally Tables[/url]
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply