This content has been marked as final. Show 19 replies
are you sure? cause there's no 'PS_JOB.EMPLID'
Yes, what I have realized is that problem is not with the field, but with the group by clause. Still I am am not able to resolve it
Why are you using GROUP BY?
You don't appear to be using an aggregate function (like COUNT or SUM).
Finally, don't post unformatted code. It makes it difficult to read.
Edited by: Paul Horth on 03-May-2012 02:30
select a.supervisor_id ,a.emplid ,b.name ,c.email_addr ,(convert(char(10) ,a.job_entry_dt ,121)) from ps_job a ,ps_names b ,ps_email_addresses c where a.effdt = (select max(a_ed.effdt) from ps_job a_ed where a.emplid = a_ed.emplid and a.empl_rcd = a_ed.empl_rcd and a_ed.effdt <= substring(convert(char ,getdate() ,121) ,1 ,10) and a.effseq = (select max(a_es.effseq) from ps_job a_es where a.emplid = a_es.emplid and a.empl_rcd = a_es.empl_rcd and a.effdt = a_es.effdt) and a.emplid = b.emplid and b.effdt = (select max(b_ed.effdt) from ps_names b_ed where b.emplid = b_ed.emplid and b.name_type = b_ed.name_type and b_ed.effdt <= a.effdt) and b.emplid = c.emplid) group by a.supervisor_id
GROUP BY a.supervisor_id, a.emplid, b.NAME, c.email_addr
@Paul Horth : I am new, I wont do it next time. Thanks
@1B: I've tried it already, it doesn't work
Again, why use GROUP BY. What happens if you remove it.
try this:1 person found this helpful
/* Formatted on 2012/05/03 16:16 (Formatter Plus v4.8.8) */ SELECT a.supervisor_id, a.emplid, b.NAME, c.email_addr, count(*) group_function FROM ps_job a, ps_names b, ps_email_addresses c WHERE a.effdt = (SELECT MAX (a_ed.effdt) FROM ps_job a_ed WHERE a.emplid = a_ed.emplid AND a.empl_rcd = a_ed.empl_rcd AND a_ed.effdt <= substring (CONVERT (CHAR, getdate (), 121), 1, 10) AND a.effseq = (SELECT MAX (a_es.effseq) FROM ps_job a_es WHERE a.emplid = a_es.emplid AND a.empl_rcd = a_es.empl_rcd AND a.effdt = a_es.effdt) AND a.emplid = b.emplid AND b.effdt = (SELECT MAX (b_ed.effdt) FROM ps_names b_ed WHERE b.emplid = b_ed.emplid AND b.name_type = b_ed.name_type AND b_ed.effdt <= a.effdt) AND b.emplid = c.emplid) GROUP BY a.supervisor_id, a.emplid, b.NAME, c.email_addr
I am trying to sort supervisor_id , so that emplid under those supervisor id are all at a place.
supervisor id emplid
It worked. Thanks
Mark it as Answered then.
No, no no!1 person found this helpful
Don't use group by to order your results!
Use ORDER BY.
It is vitally important you do not rely on group by for this.
Any ordering you may observe is a non-reliable side-effect of what grouping normally does.
It could change at any time and give you non-ordered results.
Edited by: Paul Horth on 03-May-2012 03:33
I have noticed you have marked the question as answered.
Please let me know you got my last post telling you NOT to use GROUP BY to order your results.
dont mind. next time prehaps he will find the correct forum also.
See use of functions convert and GETDATE(). Transact-SQL imho
GROUP BY (Transact-SQL)
"The GROUP BY clause does not order the result set. Use the ORDER BY clause to order the result set"
Edited by: chris227 on 03.05.2012 04:24
I've changed the function:
SELECT a.supervisor_id, a.emplid, b.NAME, c.email_addr, count(*) group_function
.. May be he is learning about group by.