SQL query for extracting a single row from multiple rows-CodePudding

We had a table previously for user information which contained the pinHash as well

id	userId	seed	assetType	pinHash
1	110	a12H	Bitcoin	q23es
2	111	r23s	Bitcoin	e2w12
3	111	y36w	Ethereum	e2w12

So, for the same userId the pinHash would be same for all the assetTypes.

Now, we are migrating to another table

id	userId	pinHash
1	110	q23es
2	111	e2w12

Which SQL can be used to get all the records with for this migration i.e. distinct userId with pinHash.

N.B: I already did this migration using spring boot where I take all the rows from the first table and then use a HashMap to put only one row of userId in the new table. Still, I would love a SQL for which only distinct userId rows would come as result.

CodePudding user response：

Assuming you always want to report the minimum id from each set of duplicates, we can use aggregation here:

SELECT MIN(id) AS id, userId, pinHash
FROM yourTable
GROUP BY userId, pinHash;

CodePudding user response：

Your data

CREATE TABLE TEST(
   id        INTEGER  NOT NULL 
  ,userId    INTEGER  NOT NULL
  ,seed      VARCHAR(5) NOT NULL
  ,assetType VARCHAR(9) NOT NULL
  ,pinHash   VARCHAR(5) NOT NULL
);
INSERT INTO TEST
(id,userId,seed,assetType,pinHash) VALUES 
(1,110,'a12H','Bitcoin','q23es'),
(2,111,'r23s','Bitcoin','e2w12'),
 (3,111,'y36w','Ethereum','e2w12');

use Row_number function

SELECT id,
       userid,
       pinhash
FROM   (SELECT id,
               userid,
               seed,
               assettype,
               pinhash,
               Row_number ()
                 OVER (
                   partition BY userid, pinhash
                   ORDER BY id ASC ) rn
        FROM   test) T
WHERE  rn = 1