SQL medium question

First Table:
SSN, open_datetime, close_datetime, priority

Second Table:
ID, time, c_time, c_response, c_id

I need all the ssn, with an open date between 4/1/2010 and 4/30/2010, with a priority of 1 and need the c_response and time from table two.  Only problem is that second table has multiple records for the same c_id.  I need the first record of the second table based on c_id = SSN and c_time.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

two step process:
step one,
join them together and put the result into a temp table.
step two,
group them with a max(id) on temp table.
then you have your result.

If you can not finish a process in one step, try to do it in two, three, four....steps.
this is the key to be a master in sql script.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
try this query..

SELECT            f.SSN, f.open_datetime, f.close_datetime, Max(s.time) AS 'time', Max(c_response) AS 'response'
FROM            FirstTable f
INNER JOIN      SecondTable s ON s.c_id=f.SSN
WHERE            f.open_datetime between '4/1/2010' AND '4/30/2010' AND f.priority=1
GROUP BY       f.SSN, f.open_datetime, f.close_datetime
Vipul Patel.NET ExpertCommented:
below query might be helpful.

;WITH Level1
      SELECT f.SSN
                  ,ROW_NUMBER() OVER (PARTITION BY s.c_id ORDER BY s.c_time ASC) AS RowNumber
      FROM FirstTable f
      INNER JOIN SecondTable s ON s.c_id=f.SSN
      WHERE (f.open_datetime between '4/1/2010' AND '4/30/2010' AND f.priority=1)
SELECT * FROM Level1 WHERE RowNumber = 1
needpleaseAuthor Commented:
timexist, I would prefer in one query.

rushShah, I get the following error, "The text, ntext, and image data types are invalid in this subquery or aggregate expression".

it means you can't use c_response or c_time in aggregate function text, ntext or image datatype..

so try this CTE query & let me know if still there is an error..

    SELECT          f.SSN, f.open_datetime, f.close_datetime, Max(s.ID) AS 'ID'
	FROM            FirstTable f
	INNER JOIN      SecondTable s ON s.c_id=f.SSN
	WHERE           f.open_datetime between '4/1/2010' AND '4/30/2010' AND f.priority=1
	GROUP BY		f.SSN, f.open_datetime, f.close_datetime

SELECT	c.SSN, c.open_datetime, c.close_datetime, s.time AS 'time', c_response AS 'response'
INNER JOIN SecondTable s ON c.ID=s.ID

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.