Sitemap

Amicuk Programming Answers

Reorder with just a single Query?

-0001-11-30   Views:0

Advertisement

If I have a table like ID | Desc | Sortorder 1 One 3 4 Two 2 6 Three 1 Is there a way I can use a single query to change the Sortorder to 1,2,3 based on my ORDER BY ID without having to write a script?and a complete example SQL> create table   x as  

If I have a table like
ID | Desc | Sortorder
1 One 3
4 Two 2
6 Three 1
Is there a way I can use a single query to change the Sortorder to 1,2,3 based on my ORDER BY ID without having to write a script?

The replay answer
Advertisement
and a complete example
SQL> create table   x as
  2  (
  3  select 1 id, 'One' descr,  3 sortorder from dual union all
  4  select 4 id, 'Two' descr,  2 sortorder from dual union all
  5  select 6 id, 'Three' descr,  1 sortorder from dual
  6  )
  7  /
Table created.
SQL> select *
  2    from x
  3  /
        ID DESCR  SORTORDER
         1 One            3
         4 Two            2
         6 Three          1
SQL> merge into x
  2  using (
  3  select id
  4       , row_number() over (order by id
  5                           ) rn
  6       , rowid rid
  7    from x) x_orig
  8   on (x.rowid = x_orig.rid)
  9   when matched then
10   update set sortorder = x_orig.rn
11  /
3 rows merged.
SQL> select *
  2    from x
  3  /
        ID DESCR  SORTORDER
         1 One            1
         4 Two            2
         6 Three          3

Go to See the other 5 answers

Reorder with just a single Query?

Category:DefaultRelease time:-0001-11-30Views:130

If I have a table like ID | Desc | Sortorder 1 One 3 4 Two 2 6 Three 1 Is there a way I can use a single query to change the Sortorder to 1,2,3 based on my ORDER BY ID without having to write a script?and a complete example SQL> create table   x as  [More]

How to use multiple hierarchies for a single char in single query

Category:DefaultRelease time:2015-10-11Views:130

Hi, Is there any way that we can use multiple hierarchies for a single char in single query. I tried and it just allows me to select one hierarchy even if I use hierarchy variable. I have a requirement where user wants to see information related to a[More]

Multiple databases in a single query

Category:DefaultRelease time:2015-10-11Views:130

I need to query multiple databases in a single query. e.g. Database d1 contains table t1 with column id Database d2 contains table t2 with column id I need a query like SELECT * FROM t1, t2 WHERE t1.id = t2.id; is this possible in jdbc? if yes can an[More]

Populate Multiple Items bundled in a single query

Category:DefaultRelease time:2015-10-11Views:130

Hello, I am trying to create a report in which I have mulitple items that I want to populate. All of these use the same query but use different rows in the query to populate themselves. My question is:- Is there a way to bundle the item assignment in[More]

How to make the query in one single query? No Union please....

Category:DefaultRelease time:2015-10-11Views:130

Hi, I have sets of around 18 queries which is finding counts in many scenarios. I wanted to have a single query for this. which diplays the result in a single row.Is there a way , I heared analytical functions are good options ,but I am new to Oracle[More]

Multiple conditin on a single column in a single query

Category:DefaultRelease time:2015-10-11Views:130

i want to select data from the table using diff conditions on a single column for eg: i need emp details like whose having empno as 5-15,19-45,50-89 ..etc this how can i give a condition in a single query we can use in or between for 2 or 3 condition[More]

All selected 'n' consecutive rows in retrieved in a single query

Category:DefaultRelease time:2015-10-11Views:130

hello, I have table T with 50,000 rows create table T (student_id number, class_id number, quiz_id number, marks number) some sample rows like INSERT INTO T VALUES (1,1, 1, 50); INSERT INTO T VALUES (2,2, 2, 40); INSERT INTO T VALUES (3,1, 3, 34); IN[More]

Is it possible to combine 2 different reports in a single Query?

Category:DefaultRelease time:2015-10-11Views:130

Dear All, What am I working at? I produced a Query for Debtors Aged Analysis which mimics the Official SAP B1 8.8 Aging Report. It ages the outstanding invoices by Posting Date ( RefDate  in JDT1) I also made another Report which ages outstanding amo[More]

Double aggregation in a single query block doesn't make any sence.

Category:DefaultRelease time:2015-10-11Views:130

How can I argue with something that apparently has been cast in stone by ANSI SQL committee? Well the answer is famous: "Search any park in any city: you'll find no statue of committee". OK, why select count(1) from ( select deptno from emp grou[More]

How to calculate the individual sums of multiple columns in a single query

Category:DefaultRelease time:2015-10-11Views:130

Hello, Using Oracle 11gR2 on windows 7 client. I have a question on calculating sum() on multiple columns on different columns and store the results in a view. Unfortunately I could not post the problem here as it keeps on giving error "Sorry, this c[More]

Tricky SQL query... how to get all data in a single query?

Category:DefaultRelease time:2015-10-11Views:130

create table employee_definition (def_id number, def_name varchar(50)); insert into employee_definition values (100, 'EMAIL'); insert into employee_definition values (200, 'MOBILE_PHONE'); insert into employee_definition values (300, 'HOME_PHONE'); S[More]

Hot
I have a 1st generation MBP running OS X Tiger (that's right, 10.4.11) and it is really showing it's age, mostly when I'm switching back and forth between multiple programs and when I'm browsing online. It will get tremendously slow (sometimes I'll h [More]
I am trying to deploy a simple Hello World EJB onto Oracle 8.1.7 using Jdeveloper 3.2. I get the following error when deploying : Loading EJB Jar file and Comm Stubs Jar file... error: loadJava has failed to load some classes; Please check trace file [More]
Hi, The folowing process takes 50% cpu and sometimes it takes 100% cpu in Oracle 10g AS (OS version 5.8), what is the way to get ride of this problem? /ORACLE_HOME/jdk/jre/bin/java -server -cp /ORACLE_HOME Also, please let me know how to get into ora [More]
Hello coleagues ! I ve picked this machine for keepin it with,  had it setup with win7 and latest bios / drivers. I have vid drivers directly from intel and what ive noticed - bby spec this 3150 gpu should support opengl spec 1.5 but in reality it is [More]
Hello gurus, when looking for certain equipments using F4 (program RIEQUI20), I can search for all equipments of a certain manufacturer (field HERST). Unfortunatelly this search is always case-sensitive, i.e when I enter "BOSCH", equipments with [More]
We currently use both infotype 0008 Basic Pay, 0007 Planned Working time, and 0315 timesheet defaults. So we can tell what % of time people should work each week/month. We would like to store an additional target, of say 80% of time being booked to a [More]
I have added some photos into a file under my document library (I can switch them to a image folder if needed), and I want to be able to see a thumbnail preview of the image.  I am having issues finding how to make the thumbnail view an option.  I am [More]
Hello, I'm having an issue with audio playback.  Once I publish the project, the audio files play back double, with the second one starting about 3 seconds after the first.  If I pause the project, one of the audio files keeps playing.  I removed the [More]
i doing email in the apple id but do not opean in the i pad "[email protected]"cannot be used to unlock this ipad" but i sing in apple idIs this a used iPad? If yes, you need to get the person who had it before you to clear the Activation L [More]
I have an iPhone 3G running 4.2.1 and I'd like to be able to patch the location-tracking without jailbreaking my phone. I noticed the latest patch that fixes the 4 and 3GS doesn't support my phone. Does anyone know (Apple?) if there will be a patch f [More]