An Oracle query SQL Problem

My problem is as follows:

There is a table t, field c1, c2, c3, c4, which requires a random selection from the repeated values of c1 to form a result set. How to implement it?

For example, the record is as follows:

Aa1 bb1 maid
Aa1 bb2 cc2 dd2
Aa1 bb3 cc3 dd3
Aa2 bb4 cc4 dd4
Aa2 bb5 cc5 dd5
Aa2 bb6 cc6 dd6
.....

The result is that a record is randomly selected from the value aa1, and a record is randomly selected from aa2. So on, the result set is obtained. How can I use a single SQL statement?

The lastwinner statement is:

Select B. c1, B. c2, B. c3, B. c4 from
(Select c1, 0 as lb, count (*) as ub from t2 group by c1),
(Select c1, c2, c3, c4, row_number () over (partition by c1 order by c2, c3, c4) rn from t2) B
Where a. c1 = B. c1
And ceil (dbms_random.value (a. lb, a. ub) = B. rn;

This is a good idea. The first subquery is grouped by field c1 to obtain the upper and lower limits of the number of records corresponding to each value of c1. The second subquery uses the row_number () analysis function to partition by field c1, records with the same c1 value are numbered. The outer query associates the result sets of the two subqueries with the c1 field. Each c1 value has a random value between the upper and lower limits of the number of records, the ceil function can be used to obtain the boundary values at both ends.

After analyzing the preceding query statement, you can seeJackywood statementsSimpler:

Select *
From (select C1, C2, C3, C4,
Row_number () over (partition by C1 order by dbms_random.value) as Rn
From T2)
Where Rn = 1;

Apply the random value directly to the order by parameter of the row_number () analysis function, which is better and simpler.

Both solutions are good, and the execution efficiency is acceptable. The second solution is more direct and simpler. In fact, such a requirement is quite common. For example, there is a song library song, each song has a unique songid, and each singer has multiple songs, now, you want to randomly select a song from the list of singers and songs that each singer sings:

Select *
From (select T .*,
Row_number () over (partition by singer order by dbms_random.value) as Rn
From songs T)
Where Rn = 1;

Or

Select * from
(Select singer, 0 as LB, count (*) as UB from songs group by singer),
(Select t. *, row_number () over (partition by singer order by songid) rn
From songs t) B
Where a. singer = B. singer
And ceil (dbms_random.value (a. lb, a. ub) = B. rn;

The key to solving the problem is the use of row_number () analysis functions.

First, we will briefly introduce the relevant content.

ROWID, ROWNUM, and ROW_NUMBER ()
(1) rowid indicates the physical sequence number of the record;
(2) rownum only refers to the sequence number of the record;
(3) row_numer (order by colunmx) is a function that may be used to specify sequence numbers for records;
And row_numer (partition by colunmx order by colunmy) means to specify the sequence number for colunmx in the colunmy order. The value of each colunmx starts from 1 to the last one, therefore, it is easy to find duplicate records!

The following is a common "re-query" Application of rowid and row_number:
(1 ),
SELECT * FROM table1 where rowid! = (Select max (ROWID) FROM table1 D
WHERE table1.colunm1 = D. colunm1 AND table1.colunm2 = D. colunm2 );
(2 ),
SELECT * FROM (
SELECT * FROM
(SELECT a. *, ROW_NUMBER () OVER (partition by colunm1, colunm2 order by colunmx) B FROM table1
) Aa
WHERE aa. B = 1;

Note: Query Repeated Records in column1 and column2 in table 1.