# Thread: Writing a query - 10g

1. Registered User
Join Date
Aug 2008
Posts
464

## Unanswered: Writing a query - 10g

Hi

I have an table containing hourly values and I want to write a query to extract a couple of columns based on the maximum value of one of the extracted columns. The query I wrote is:

Code:
```SELECT * FROM
(SELECT DATETIME,NODE,COL1 AA, ((COL2+COL3)/COL1) BB
FROM
TABLE
WHERE DATETIME BETWEEN TRUNC(SYSDATE)-3 and TRUNC(SYSDATE)-2-1/86400 AND NODE='ABC')
WHERE COL1 in
(SELECT MAX(COL1)
FROM
(SELECT DATETIME, COL1
FROM TABLE
WHERE DATETIME BETWEEN TRUNC(SYSDATE)-3 and TRUNC(SYSDATE)-2-1/86400 AND NODE='ABC'))```
The above gives one row in the form:

Code:
`DATETIME NODE AA BB`
This query doesn't look very efficient to me. Moreover, I can only run the query for one day.

Is there a way I can get the above-mentioned for more than 1 day? I need to query/compute values based on the maximum value of COL1 out of 24 hourly values for each day.

Regards
Shajju

2. Registered User
Join Date
Oct 2002
Location
Cape Town, South Africa
Posts
253
Essentially yes, that is the way you would have to do it. But you could make the query look a little simpler. Here is my re-write which should do exactly the same thing using the exact same resources:
Code:
```SELECT DATETIME, NODE, COL1 AA, ((COL2+COL3)/COL1) BB
FROM TABLE
WHERE DATETIME >= TRUNC(SYSDATE)-3
AND DATETIME <  TRUNC(SYSDATE)-2
AND NODE='ABC'
AND COL1 = (SELECT MAX(COL1)
FROM TABLE
WHERE DATETIME >= TRUNC(SYSDATE)-3
AND DATETIME <  TRUNC(SYSDATE)-2
AND NODE='ABC'))```
I would expect that you would have a two column index on TABLE(DATETIME, COL1) at a minimum. Obviously NODE is also an index candidate.

Use explain plan to evaluate query cost.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•