GROUP BY clause problem (merged 3) [message #376940] |
Fri, 19 December 2008 04:08  |
waqasbhai
Messages: 118 Registered: August 2008 Location: Pakistan
|
Senior Member |
|
|
HI,
I have a following query:
SELECT col1,
col2
FROM a,
b
GROUP BY col1,
col2
ORDER BY col1
Now i want to add another column in ORDER BY clause, eg
SELECT col1,
col2
FROM a,
b
GROUP BY col1,
col2
ORDER BY col1,
col3
But it gives an error saying that col3 is not a GROUP by expression.
Actually I don't want to add that column in GROUP BY clause because it changes the grouping.
So is there any way to use the col3 in ORDER BY without effecting the grouping...???
|
|
|
|
|
Re: GROUP BY clause problem (merged) [message #376971 is a reply to message #376940] |
Fri, 19 December 2008 05:12   |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
Quote: | Any solution to resolve this issue?
|
Yes, specify exactly, what you want, because the the current requirement is a little ambiguous (politely said).
Imagine following data: COL1 COL2 COL3
---- ---- ----
1 1 1
1 1 8
1 2 5
1 2 6
1 2 7
Which of COL2 (1 or 2) is "bigger" based on COL3 value and why?
|
|
|
Re: GROUP BY clause problem (merged) [message #376974 is a reply to message #376940] |
Fri, 19 December 2008 05:20   |
waqasbhai
Messages: 118 Registered: August 2008 Location: Pakistan
|
Senior Member |
|
|
Okay let me explain it:
I have one column, say col1, which I am using in ORDER BY cluase, as
ORDER BY col1
Now i want to put another column in the ORDER BY cluase. It is actually a hiredate of an employee.
ORDER BY col1, hiredate.
Now col1 is already present in GROUP BY cluase and SQL is saying me to put hiredate in the GROUP BY cluase as well, which i don't want to do.
I hope this will give you a good idea of the situaion.
|
|
|
|
|
Re: GROUP BY clause problem (merged) [message #376982 is a reply to message #376979] |
Fri, 19 December 2008 05:41   |
Olivia
Messages: 519 Registered: June 2008
|
Senior Member |
|
|
No....
Post what you did. You should let know what you want to achieve.
SELECT department_id dept_id, job_id, SUM(salary)
FROM employees
GROUP BY department_id, job_id Order by department_id, job_id;
SELECT department_id dept_id, SUM(salary)
FROM employees
GROUP BY department_id, Order by department_id, job_id;
Regards,
Oli
[Updated on: Fri, 19 December 2008 05:51] Report message to a moderator
|
|
|
Re: GROUP BY clause problem (merged) [message #376983 is a reply to message #376940] |
Fri, 19 December 2008 05:49   |
waqasbhai
Messages: 118 Registered: August 2008 Location: Pakistan
|
Senior Member |
|
|
ok have a look at the original query:
SELECT Initcap(Last_Name)
||' , '
||Initcap(First_Name) NAME
FROM Employee a,
Overtime_List b,
ot_JobTitle c,
ot_dept d,
ot_Shift e
WHERE a.emp_Id = b.uuId
AND b.jt_No = c.jt_No
AND b.dept_No = d.dept_No
AND b.Shift_No = e.Shift_No
GROUP BY Initcap(Last_Name)
||' , '
||Initcap(First_Name),
b.ytd_New
ORDER BY b.ytd_New
Now i want to add hiredate in ORDER BY CLAUSE
|
|
|
Re: GROUP BY clause problem (merged) [message #376985 is a reply to message #376940] |
Fri, 19 December 2008 05:52   |
cookiemonster
Messages: 13965 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
We're getting side tracked here.
As Michel and Flyboy already explained you can not order by a column you don't group by. There are NO exceptions to this rule.
Re-read Flyboys post and think carefully about the question he asks at the end.
|
|
|
|
|