Quote:
|
Please define "first". Rows in a table do not have any intrinsic ordering.
|
Yes, many experts metined repeatedly that point.
Because, DB2(and almost all relational databases) don't expect any order of rows in a table,
even if you supplied or showed rows with specific order.
The logical order of rows have meaning only in a query using ORDER BY clause explicitly.
Here are examples.
Exampel 1:
'ndb' was selected for a row (col1, col2) = (1, 3).
Code:
------------------------------ Commands Entered ------------------------------
SELECT col1 , col2 , col3
FROM (SELECT s.*
, ROW_NUMBER()
OVER(PARTITION BY col1, col2) rn
FROM dform.sample_data s
) t
WHERE rn = 1
;
------------------------------------------------------------------------------
COL1 COL2 COL3
----------- ----------- -------
1 1 abc
1 2 rst
1 3 ndb
3 record(s) selected.
Exampel 2:
'mnv' was selected for a row (col1, col2) = (1, 3).
Code:
------------------------------ Commands Entered ------------------------------
ALTER TABLE dform.sample_data
ADD PRIMARY KEY (col1 , col2 , col3)
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
SELECT col1 , col2 , col3
FROM (SELECT s.*
, ROW_NUMBER()
OVER(PARTITION BY col1, col2) rn
FROM dform.sample_data s
) t
WHERE rn = 1
;
------------------------------------------------------------------------------
COL1 COL2 COL3
----------- ----------- -------
1 1 abc
1 2 rst
1 3 mnv
3 record(s) selected.
DDL and data:
Code:
------------------------------ Commands Entered ------------------------------
CREATE TABLE dform.sample_data
( col1 INTEGER NOT NULL
, col2 INTEGER NOT NULL
, col3 VARCHAR(7) NOT NULL
);
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
------------------------------ Commands Entered ------------------------------
INSERT INTO dform.sample_data
VALUES
(1 , 1 , 'abc')
, (1 , 1 , 'dev')
, (1 , 2 , 'rst')
, (1 , 3 , 'ndb')
, (1 , 3 , 'mnv')
;
------------------------------------------------------------------------------
DB20000I The SQL command completed successfully.
dBforums
>
select self join, distinct values
09-10-10, 16:59
Linear Mode
