issue with select - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

issue with select

Author

Topic

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-11 : 20:31:21

I have been trying these for loang time now..please somebody help me with this query

SELECT DISTINCT W.[emp_id], W.[order_id], W.[grade],
W.[test_info], W.[completed_date]
FROM tbl_emp_details W

INNER JOIN
( SELECT emp_id,MAX(order_id) as order_id
FROM tbl_emp_details
GROUP BY emp_id

)S

ON (S.emp_id = W.emp_id)
AND (S.order_id = W.order_id)
AND NOT(W.emp_id IS NULL)

ORDER BY W.[loan_number] ASC

emp_id          order_id grade  test_info completed_date
0001266360	1294266	GRADE A	NULL	NULL
0001273499	1851623	Grade C	NULL	NULL
0001278316	1887332	Grade B	NULL	NULL
0001278514	1981682	Grade C	NULL	NULL
0001278514	1981682	Grade C	NULL	6/28/2004
0001278514	1981682	Grade C	NULL	6/30/2004

Here after the query iam getting this as the result..but there is duplicate emp_ids here..I want
basically if it has date then the recordset with date(max-date) and there should be always distinct emp_id in the select

clarkbaker1964
Constraint Violating Yak Guru

428 Posts

Posted - 2004-10-11 : 21:43:37

You need to encapcelate the Completed_Date with the Max function in the Sub query rather than in the outer query.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-11 : 22:00:51

Can u please show me how to write the code....

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-11 : 22:03:05

quote:
Originally posted by sqllearner

SELECT w.[emp_id], W.[order_id], W.[grade],
W.[test_info], max(W.[completed_date])
FROM tbl_emp_details W

INNER JOIN
( SELECT emp_id,MAX(order_id) as order_id
FROM tbl_emp_details
GROUP BY emp_id

)S

ON (S.emp_id = W.emp_id)
AND (S.order_id = W.order_id)
AND NOT(W.emp_id IS NULL)
group by w.[emp_id], W.[order_id], W.[grade],W.[test_info]

if you're going to use max, use group by since you have other fields which are not in aggregates. no need for distinct since you have the group by clause.

sqllearner
Aged Yak Warrior

639 Posts

Posted - 2004-10-11 : 22:27:24

Brilliant..Got it thanks a lot.....Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources