SQL query for extracting a single row from multiple rows

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.

>Solution :

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;

Leave a Reply