nQuote
asked on
PL/SQL query help
I need to write a SQL as:
select max(Col1), Col3, Col4
from Tbl1
where Col2=5
I then need the corresponding Col3 and Col4 values for the max(Col1) record. I tried several combinations of PARTITION/OVER BY but it didn't work. I was able to resolve it using two SQLs. I would like to do it in one SQL instead of doing it in two and doing a join between the PKs. Thanks.
select max(Col1), Col3, Col4
from Tbl1
where Col2=5
I then need the corresponding Col3 and Col4 values for the max(Col1) record. I tried several combinations of PARTITION/OVER BY but it didn't work. I was able to resolve it using two SQLs. I would like to do it in one SQL instead of doing it in two and doing a join between the PKs. Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Some sample values in Tbl1:
Col1 Col2 Col3 Col4
1 A P X
3 B Q Z
2 C R Z
So I want the 2nd row.
Col1 Col2 Col3 Col4
1 A P X
3 B Q Z
2 C R Z
So I want the 2nd row.
Your sample data makes no sense. According to your specification, col2 should be a number and you should be looking for rows with a 5 in that column.
Here is the query I ran:
The only modification from the original is that I added col2 and removed the where clause for col2=5 because that doesn't make sense given your sample data.
That query comes out with the row that you asked for. There is no need for the MAX function, the order by and row number functions handle that.
If there is an issue, please provide sample data that shows the issue. The sample data you provided gives the correct result.
Here is the query I ran:
SELECT col1,
col2,
col3,
col4
FROM (SELECT Row_number()
over (
ORDER BY col1 DESC) rn,
col1,
col2,
col3,
col4
FROM tbl1) a
WHERE rn = 1;
The only modification from the original is that I added col2 and removed the where clause for col2=5 because that doesn't make sense given your sample data.
That query comes out with the row that you asked for. There is no need for the MAX function, the order by and row number functions handle that.
If there is an issue, please provide sample data that shows the issue. The sample data you provided gives the correct result.
You could also try this approach:
You could arbitrarily limit to one record using rownum if only one record is required, but you don't specify what should happen for this circumstance (and if this circumstance exists which record is the right record?)
SELECT
col3
, col4
FROM table1
WHERE col1 = (SELECT MAX(col1) FROM table1)
;
result
| COL3 | COL4 |
|------|------|
| Q | Z |
NOTE IF col1=3 exists for more than one record then each of those will be listed, You could arbitrarily limit to one record using rownum if only one record is required, but you don't specify what should happen for this circumstance (and if this circumstance exists which record is the right record?)
ASKER
Thanks. Might re-visit this later. Don't have time to verify now.
ASKER