View previous topic :: View next topic
|
Author |
Message |
Raja12752
New User
Joined: 18 Jul 2006 Posts: 28
|
|
|
|
Hi
Is there any wrong with this sql query?
SELECT DATE(MAX(B.NOTE_RVSD_TMSTMP)), B.NTSBJ_CD
FROM KI_CUST_NOTE A
,KI_NOTE B
WHERE A.CU_ID = :HOSTVAR-CU-ID
AND A.NOTE_ID = B.NOTE_ID
AND B.NTSBJ_CD IN ('EA','TT')
Here I want the maximum time stamp with NTSBJ_CD. And the query should throw only one row.
Thanks. |
|
Back to top |
|
|
tarun_bhardwaj
New User
Joined: 18 Jul 2003 Posts: 39 Location: delhi
|
|
|
|
This query will not work since there is a missing 'GROUP BY' clause. In a query, if an aggregate function (like 'MAX') is used with another column, you need to provide the 'GROUP BY' clause. |
|
Back to top |
|
|
tarun_bhardwaj
New User
Joined: 18 Jul 2003 Posts: 39 Location: delhi
|
|
|
|
This query should work for you:
Code: |
SELECT DATE(B.NOTE_RVSD_TMSTMP), B.NTSBJ_CD
FROM KI_CUST_NOTE A
,KI_NOTE B
WHERE A.CU_ID = :HOSTVAR-CU-ID
AND A.NOTE_ID = B.NOTE_ID
AND B.NTSBJ_CD IN ('EA','TT')
AND B.NOTE_RVSD_TMSTMP = (SELECT MAX(B.NOTE_RVSD_TMSTMP) FROM KI_NOTE) |
|
|
Back to top |
|
|
Raja12752
New User
Joined: 18 Jul 2006 Posts: 28
|
|
|
|
Is it mandatory to use the group by clause with the two columns which we are planning to select?
Or the only one column which is in aggregate function??
ref:
SELECT DATE(MAX(B.NOTE_RVSD_TMSTMP)), B.NTSBJ_CD
FROM KI_CUST_NOTE A
,KI_NOTE B
WHERE A.CU_ID = :HOSTVAR-CU-ID
AND A.NOTE_ID = B.NOTE_ID
AND B.NTSBJ_CD IN ('EA','TT')
GROUP BY B.NOTE_RVSD_TMSTMP, B.NTSBJ_CD |
|
Back to top |
|
|
tarun_bhardwaj
New User
Joined: 18 Jul 2003 Posts: 39 Location: delhi
|
|
|
|
Hi Raja,
With reference to my 2 postings above, there are 2 ways to get the desired results:
1st:
Code: |
SELECT DATE(MAX(B.NOTE_RVSD_TMSTMP)), B.NTSBJ_CD
FROM KI_CUST_NOTE A
,KI_NOTE B
WHERE A.CU_ID = :HOSTVAR-CU-ID
AND A.NOTE_ID = B.NOTE_ID
AND B.NTSBJ_CD IN ('EA','TT')
GROUP BY B.NTSBJ_CD |
The above query would get you the date of the maximum timestamp for each NTSBJ_CD code with value EA and TT. Thus the result of the above query would get you two rows.
The second query is:
Code: |
SELECT DATE(B.NOTE_RVSD_TMSTMP), B.NTSBJ_CD
FROM KI_CUST_NOTE A
,KI_NOTE B
WHERE A.CU_ID = :HOSTVAR-CU-ID
AND A.NOTE_ID = B.NOTE_ID
AND B.NTSBJ_CD IN ('EA','TT')
AND B.NOTE_RVSD_TMSTMP = (SELECT MAX(B.NOTE_RVSD_TMSTMP) FROM KI_NOTE) |
The above query would result in just 1 row being fetched for the date of maximum timestamp for either NTSBJ_CD (with value EA or TT) which ever has the highest timestamp.
Depends on your requirement which query you want to execute. |
|
Back to top |
|
|
Raja12752
New User
Joined: 18 Jul 2006 Posts: 28
|
|
|
|
Thank You for your timely suggestion..... |
|
Back to top |
|
|
|