View previous topic :: View next topic
|
Author |
Message |
Anand Kumar
New User
Joined: 29 Aug 2007 Posts: 24 Location: chennai
|
|
|
|
Hi Experts,
I am having performance a performance issue in a query. Please can you give me your suggestions. This should be performed only with SQL and is a part of a main query.
The data in table is as follows:-
Table Name GWACTD
Code: |
Act-No(Decimal 16) Day-key(Int 4) Time(Dec12) Debit-amt(Dec18)
---------------------------------------------------------------------------------
1111 4 123456 80000
1111 5 111234 90000
3333 1 999999 12242
3333 2 343434 98779
3333 2 122123 99923 |
Required result should be one record for every Act-No, with the latest transaction alone. i.e. record with max(day key) and if more than one transaction happens on the same day, the record with the highest time stamp is required.
Hence the result of the select should be as follows:-
Result:-
Code: |
Act-No(Decimal 16) Day-key(Int 4) Time(Dec12) Debit-amt(Dec18)
---------------------------------------------------------------------------------
1111 5 111234 90000
3333 2 343434 98779 |
Please can suggest me a simple query which provides the required result.
Regards, |
|
Back to top |
|
|
Steve Davies
New User
Joined: 15 Oct 2009 Posts: 32 Location: UK
|
|
|
|
Your query doesn't sound like a 'Fine Tuning SQL' query or an SQL performance Issue - it sounds like a 'How do I write the SQL to get this output' sort of question !
Have you tried anything yourself, if so please post the SQL you have tried. |
|
Back to top |
|
|
Anand Kumar
New User
Joined: 29 Aug 2007 Posts: 24 Location: chennai
|
|
|
|
Hi Davies,
Please find the query
Code: |
SELECT
B.ACT-NO
,B.DAY-KEY
,B.DEBIT-AMT
,B.TIME
FROM(
SELECT
A.ACT-NO
,MAX(CONCAT(CAST(DIGITS(A.DAY-KEY) AS CHAR(10)),CAST(DIGITS(A.TIME) AS CHAR(12)))) AS DAY_KEY_COM
FROM GWACTD
GROUP BY
A.ACT-NO)AS AA
INNER JOIN GWACTD B
ON B.ACT-NO = AA.ACT-NO
AND B.DAY-KEY = CAST(SUBSTR(AA.DAY_KEY_COM,1,10) AS INT)
AND B.TIME = CAST(SUBSTR(AA.DAY_KEY_COM,11,12) AS DECIMAL(12,0))
|
Please let me know if there is any other way simpler way.
Regards, |
|
Back to top |
|
|
Ronald Burr
Active User
Joined: 22 Oct 2009 Posts: 293 Location: U.S.A.
|
|
|
|
I don't know if it's SIMPLER ( or even if it's more EFFICIENT ), but I would probably code it thusly:
Code: |
SELECT A.ACT-NO
,A.DAY-KEY
,A.DEBIT-AMT
,A.TIME
FROM GWACTD A
WHERE A.TIME =
(SELECT MAX(B.TIME)
FROM GWACTD B
WHERE B.ACT-NO = A.ACT-NO
AND B.DAY-KEY =
(SELECT MAX(C.DAY-KEY)
FROM GWACTD C
WHERE C.ACT-NO = A.ACT-NO))) |
|
|
Back to top |
|
|
|