Skip to content

Tag: oracle

Dense Rank grouping by IDs

I am having trouble getting my DENSE_RANK() function in Oracle to work how I would like. First, my dataset: I am trying to use the dense rank function to return results with a sequence number based on the DATE field, and grouping by ID. How I want the data to return: The query I have so far: However, this ret…

Conditionally calling sql scripts in sql plus

I have two scripts which needs to be executed depending on whether a table exists or not in my database. So I created a 3rd script as below which checks the condition and calls the respective script. [Because my installer cannot reach db and it can only call one script while installation] END; I get the below…

How can I retrieve an updated script in the oracle database?

How can I retrieve an updated script in the oracle database? An incorrect script was run in the oracle database. I want to get this script back. how can I do that? The script is as follows. Answer If you haven’t COMMITted the data then use ROLLBACK. Otherwise, your solution could be as simple as running…

Get last row per group in SQL

I have the data below, and i want to get the last row per desc the result should be like this: how can i do it in SQL, please note that database is oracle. Answer You can get the expected result with the following query:

Delete xml node with a specific CDATA

This is part of my XML: I need to delete the EO node with <![CDATA[0001FFFFFFFF]]>,across the table using Oracle. This xml is stored in XM_DATA_CACHE of XMLTYPE. Is there a way to delete based on CDATA Final output should be Answer First of all your xml is broken: second tag GoalMeasurementEORow is not …

Creating required script

My question is about select statement. I have a table called test1 and the values in it. Here my script for creating the table, and inserting values: create table test1(id number, pc number, pe number)…