Hi all,
I have a table that looks like this:
ID_1
ID_2
VALUE
xyz
mmm
0.5
abc
mmm
0.35
I would like to return only the row containing the value closest to a number that I specify, in this case, if I specify 0.3, I would like to only see
ID_1
ID_2
VALUE
abc
mmm
0.35
How would I be able to do this in TOAD? I’ve tried searching online and I’m able to find SQL queries, but they haven’t worked in TOAD. If anyone has suggestions, that would be greatly appreciated! Thanks.
If you found a query that doesn’t ‘work’ in Toad, please let us know what the query is. Toad doesn’t execute queries, it just sends them to Oracle, so that Oracle can execute them and Toad can display the result.
As to your query, there may be a more concise way to do it, but this should work:
select *
from
Given the sample data John’s query would work but it does not take in account all data cases.
Current
table value .35 - .target value .3 = 0.5
if a there was a value on the table of 2.5 that is the row brought back
Table value .25 - target value .3 = -0.5
In other words in the case of tie above and below it, like “The Price is Right” it would bring the closest row without going over as that would represent the lowest value in ascending order.
That may or may not be what you want.
Hi John,
Thanks for your reply. I just tried your query but I received an error saying a right parenthesis was missing. The error code I’m getting is ORA-00907. If the name of the ‘value’ that I need to show in my table is for example, a column named ‘Pressure’, would my query look like this?
select *
from
where value = (PRESSURE
from (SELECT PRESSURE
FROM
ORDER BY ABS(PRESSURE - 0.5 ))
where rownum = 1)
I’m new to TOAD so apologies if this seems a bit basic! Thanks.
You missed the word SELECT in a subquery…
select *
from
where value = (select PRESSURE
from (SELECT PRESSURE
FROM
ORDER BY ABS(PRESSURE - 0.5 ))
where rownum = 1)
Hi John,
I’ve just tried your suggestion but it’s still giving me the same error code.
The query is right. If there are any blank lines, remove them, and add a semicolon at the end.
This is the one I was testing with:
select *
from all_Tables
where num_rows = (select num_rows
from (SELECT num_rows
FROM all_Tables
ORDER BY ABS( num_rows - 50 ))
where rownum = 1);
It works! Thanks! I had put ‘value’ where ‘num_rows’ should have been. Is there any way to show the closest value to the number I specify for a specific group? For instance, in my database of 600 rows I have 5 rows with a common identifier. For those 5 rows, I would like to show just one row that contains the value I need (which the query above retrieves). However, for the next group of rows with a common identifier, I would also like to do the same - and so on for the entire table. I was thinking this may be possible by inserting ‘over [ID]’ somewhere, but I’m not sure exactly how to do this.
I created a table as shown in your example did some testing wanted to document the results
ID_1
ID_2
VALUE
def
mmm
0.25
When I added the above row .05 away from the value the ABS function still brought back the row you wanted.
ID_1
ID_2
VALUE
abc
mmm
0.35
When I added the row below which is now actually closer the value .03 rather than .05 the ABS function STILL brought back the abc row with the value .035.
This obviously IS NOT the closest value.
ID_1
ID_2
VALUE
ghi
mmm
0.27
Just a heads up.
[View:/cfs-file/__key/communityserver-discussions-components-files/10/junk.docx:320:240]
your screen shot in the doc shows your target value was 3. I think you meant to type in 0.3. 0.5 is closer to 3 than any of those other values.
Yes your right I forgot the decimal point before the 3. With the correct value of .3 the ABS function with rownum = 1 brings back the correct row the value of 0.27.
Sorry John your query is fine!
ribbitribbit, I think this is what you want, using my all_tables example.
For each owner in all_tables, this query will return table has the num_rows value nearest to 50. If you want the top 3 instead of just the closest match, you can change “where rank <= 1” to “where rank <= 3”.
select *
from (select num_rows, owner, table_name,
rank() over (partition by owner order by abs(num_rows - 50)) rank
from all_tables)
where rank <= 1;
Hi John,
Thanks for the solution. I tried it using my table where num_rows = PRESSURE, the table name is TABLE.PRESSUREAVG and the identifier is ID2 - shown in the table below:
ID1
ID2
PRESSURE
1A-1
1A
3.5
1A-2
1A
3
1A-3
1A
1.5
1B-1
1B
1.6
1B-2
1B
1.0
Desired results:
ID1
ID2
PRESSURE
1A-3
1A
1.5
1B-2
1B
1.0
There are more columns but they’re not relevant to structuring the query! So following on from your suggestion, my query is this:
select*
from(select PRESSURE, ID2, TABLE.PRESSUREAVG,
rank()over(partition by ID2 order by ABS(PRESSURE-0.85))rank
from TABLE.PRESSUREAVG)
where rank<=1;
Is this correct because I still get an error message of ORA-00911:invalid character. If I try to synchronize the diagram and SQL code, the query turns into this:
SELECT *
FROM (SELECT PRESSURE,
ID2,
TABLE.PRESSUREAVG,
RANK () RANK
FROM TABLE.PRESSUREAVG) INLINEVIEW_1
WHERE RANK <= 1
And then I get another error message: ORA-30484: missing window specification for this function. I’m not sure what I’m doing wrong here. It’s a bit frustrating as I know we’re on the right track!
OK, Starting with your query:
select*
from(select PRESSURE, ID2, TABLE.PRESSUREAVG,
rank()over(partition by ID2 order by ABS(PRESSURE-0.85))rank
from TABLE.PRESSUREAVG)
where rank<=1;
The first problem I see is formatting. Maybe the forum did that. But you should put some daylight in there so things look nice and are easier to read. So, adding a space before *, and indention in the subquery, and space around the OVER keyword and before the RANK alias at the end of the 2nd line. Now we’ve got
select *
from(select PRESSURE, ID2, TABLE.PRESSUREAVG,
rank() over (partition by ID2 order by ABS(PRESSURE-0.85)) rank
from TABLE.PRESSUREAVG)
where rank<=1;
The second problem I see is the table name. Normally, If I see a dot inside a table name, then the stuff before the dot is the schema and the stuff after the dot is the table name. And “TABLE” is a reserved word, so it shouldn’t be either one. But if the whole thing really is the table name, then it needs to be enclosed in double-quotes, because a dot really shouldn’t be part of a table name. So, if the table is really called TABLE.PRESSUREAVG, then you need to always refer to it as “TABLE.PRESSUREAVG” (case is important once you start using double quotes)
So now we have…
select *
from(select PRESSURE, ID2, “TABLE.PRESSUREAVG”,
rank() over (partition by ID2 order by ABS(PRESSURE-0.85)) rank
from “TABLE.PRESSUREAVG”)
where rank<=1;
The next problem…what is table name doing in the select list of the subquery? Table names should only appear in FROM clauses (except when used as aliases, but that’s not what’s happening here). Fixing that, we get…
select *
from(select PRESSURE, ID2,
rank() over (partition by ID2 order by ABS(PRESSURE-0.85)) rank
from “TABLE.PRESSUREAVG”)
where rank<=1;
and now the query runs, but it doesn’t exactly show your desired result…it shows
PRESSURE
ID2
RANK
1.5
1A
1
1
1B
1
So, to fix that, we can rearrange the from that * to what we actually want (and add ID1 to the subquery’s select list)
select ID1, ID2, PRESSURE
from(select PRESSURE, ID1, ID2,
rank() over (partition by ID2 order by ABS(PRESSURE-0.85)) rank
from “TABLE.PRESSUREAVG”)
where rank<=1;
and that gives you what you want…
ID1
ID2
PRESSURE
1A-3
1A
1.5
1B-2
1B
1
ribbbitribbit,
I am in a slow period so I am following this thread.
I was able to reproduce your table and validate John’s query.
In your last thread you made an error in converting John’s all_tables example. I created your table and data and converted John’s query try this:
select*
from(select ID1, ID2, PRESSURE, /* PRESSUREAVG Table fields /
rank()over(partition by ID2 order by ABS(PRESSURE-0.85))rank / Derived values */
from PRESSUREAVG)
where rank<=1;
It brings back the rows you specified:
ID1
ID2
PRESSURE
RANK
1A-3
1A
1.5
1
1B-2
1B
1.0
1
Also I am not sure about your Table name notation. Unless you have a schema (Oracle account) named “TABLE” that is incorrect.
If you want to fully qualify your table it would be {OWNER}.PRESSUREAVG.
If you are logging in with an account other than the table owner you don’t need to preface the tablename with owner.(like my in query example)
Hi John,
Thank you so much for your replies and help. I was able to get the query that I wanted (no error messages!) the only thing I did differently is remove the semicolon at the end of the last line (rank <=1) which may have been due to forum formatting. I should have put a different name for the table so it would not be confusing (I had to rename it due to confidentiality policies at work). The table name shows up as [SCHEMA].TABLENAME, so in the query I referred to the table as
from [SCHEMA].TABLENAME)
Again, thanks for the replies - you’ve been extremely helpful!
Thanks for verifying this Joe, please see my reply above regarding the table name - the initial notation was incorrect since I had to rename it due to confidentiality policies at work.