View previous topic :: View next topic
|
Author |
Message |
babu_hi
New User
Joined: 11 Apr 2006 Posts: 93
|
|
|
|
EXEC SQL
SELECT CNTL_TBL_NME,
MIN(CNTL_DTE),
CNTL_FLAG
INTO :CNTL-TBL-NME,
:CNTL-DTE,
:CNTL-FLAG
FROM ARW_CNTL_TABLE
WHERE CNTL_FLAG IN ('E','H')
GROUP BY CNTL_DTE
ORDER BY CNTL_DTE
END-EXEC.
please let me know what is wrong in the above query? |
|
Back to top |
|
|
kalukakkad
New User
Joined: 10 Mar 2005 Posts: 81
|
|
|
|
You should GROUP BY CNTL_TBL_NME and CNTL_FLAG.
Whenever you are using a column function, you have to GROUP BY all the other columns you are trying to fetch. |
|
Back to top |
|
|
Prajesh_v_p
Active User
Joined: 24 May 2006 Posts: 133 Location: India
|
|
|
|
I am not sure why you are grouping by CNTL_DTE and looking for min of CNTL_DTE. I think probably you will be grouping for CNTL_TBL_NME and CNTL_FLAG. Please let us know what you trying to achive.
Thanks,
Prajesh |
|
Back to top |
|
|
pranav yadav
New User
Joined: 04 Apr 2008 Posts: 9 Location: Noida, India
|
|
|
|
hi
the field used for group by should be the one that you have already selected. |
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
pranav:
Quote: |
the field used for group by should be the one that you have already selected.
|
Your statement is not completely correct. I can have a table defined with 30 columns and write the following select that is perfectly valid. You can group by any column or computed value based on a column, but you must also group by each column between your SELECT and FROM that is not an aggregate function.
Code: |
SELECT col25
FROM table
WHERE col21 = 'Y'
GROUP by col1, col25.
|
|
|
Back to top |
|
|
stodolas
Active Member
Joined: 13 Jun 2007 Posts: 632 Location: Wisconsin
|
|
|
|
Quote: |
Whenever you are using a column function, you have to GROUP BY all the other columns you are trying to fetch. |
Not quite, see above. When you use a function in your select list it is a materialized column. But, if that function is an aggregate function, you don't need to specify it in the GROUP BY clause. |
|
Back to top |
|
|
pranav yadav
New User
Joined: 04 Apr 2008 Posts: 9 Location: Noida, India
|
|
|
|
stodlas,
thanks for correcting me. |
|
Back to top |
|
|
|