Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>you can use analytics to define groups of contiguous rows:</p> <pre><code>SQL&gt; SELECT old_val, new_val, updflag, workno, 2 SUM(gap) over(ORDER BY old_val, new_val) grp 3 FROM (SELECT t.*, 4 CASE 5 WHEN new_val = lag(new_val) 6 over(ORDER BY old_val, new_val) 7 OR old_val = lag(old_val) 8 over(ORDER BY old_val, new_val) 9 THEN 10 0 11 ELSE 12 1 13 END gap 14 FROM Test_Table t); OLD_VAL NEW_VAL UPDFLAG WORKNO GRP ------- ------- ---------- ---------- ---------- 1 20 0 0 1 2 20 0 0 1 2 30 0 0 1 3 30 0 0 1 4 40 0 0 2 4 40 0 0 2 </code></pre> <p>The inner SELECT builds a "GAP" column that equals 1 when the current row is not in the same group as the preceeding.</p> <p>The outer SELECT uses a running total over the gap column to get the group number.</p> <h3>Edit 2</h3> <p>You can't add the FOR UPDATE clause directly to the query because of the analytic functions. You can query the base table directly however:</p> <pre><code>SQL&gt; WITH t_new AS ( 2 SELECT t_rowid, old_val, new_val, updflag, workno, 3 SUM(gap) over(ORDER BY old_val, new_val) grp 4 FROM (SELECT t.*, t.rowid t_rowid, 5 CASE 6 WHEN new_val = lag(new_val) 7 over(ORDER BY old_val, new_val) 8 OR old_val = lag(old_val) 9 over(ORDER BY old_val, new_val) 10 THEN 11 0 12 ELSE 13 1 14 END gap 15 FROM test_table t) 16 ) 17 SELECT * 18 FROM test_table 19 WHERE ROWID IN (SELECT t_rowid 20 FROM t_new 21 WHERE grp = (SELECT grp 22 FROM t_new t2 23 WHERE t2.new_val = ' 20' 24 AND t2.old_val = '1')) 25 FOR UPDATE; OLD_VAL NEW_VAL UPDFLAG WORKNO ------- ------- ---------- ---------- 1 20 0 0 2 20 0 0 2 30 0 0 3 30 0 0 </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POadvanced select in Stored Procedure
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USVincent Malgrat
    1. USVincent Malgrat
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POadvanced select in Stored Procedure
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COoh cool didnt know there were a Function to get the previous values. well this works well, but would it be possible to alter it so, that only the Rows with GRP 1 is showed?
      singulars
      1. PO
      1. USdomiSchenk
    2. CO@Auro: I updated my answer with an example, hope this helps
      singulars
      1. PO
      1. USVincent Malgrat
    3. COah its works :D i changed the `WHERE t2.new_val = ' 20' AND t2.old_val = '1'` with `where rownum = 1` it has the same effect.
      singulars
      1. PO
      1. USdomiSchenk
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload