Nicholas Kyriakides Nicholas Kyriakides - 9 days ago 4
SQL Question

Selecting only distinct rows based on a column in Knex

I'm using Knex, a pretty nice SQL builder.

I've got a table called

Foo
which has 3 columns

+--------------+-----------------+
| id | PK |
+--------------+-----------------+
| idFoo | FK (not unique) |
+--------------+-----------------+
| serialNumber | Number |
+--------------+-----------------+


I'd like to select all rows with
idFoo IN (1, 2, 3)
.

However I'd like to avoid duplicate records based on the same
idFoo
.

Since that column is not unique there could be many rows with the same
idFoo
.

A possible solution



My query above will of course return all with
idFoo IN (1, 2, 3)
, even duplicates.

db.select(
"id",
"idFoo",
"age"
)
.from("foo")
.whereIn("idFoo", [1, 2, 3])


However this will return results with duplicated
idFoo
's like so:

+----+-------+--------------+
| id | idFoo | serialNumber |
+----+-------+--------------+
| 1 | 2 | 56454 |
+----+-------+--------------+
| 2 | 3 | 75757 |
+----+-------+--------------+
| 3 | 3 | 00909 |
+----+-------+--------------+
| 4 | 1 | 64421 |
+----+-------+--------------+


What I need is this:

+----+-------+--------------+
| id | idFoo | serialNumber |
+----+-------+--------------+
| 1 | 2 | 56454 |
+----+-------+--------------+
| 3 | 3 | 00909 |
+----+-------+--------------+
| 4 | 1 | 64421 |
+----+-------+--------------+


I can take the result and use Javascript to filter out the duplicates. I'd specifically like to avoid that and write this in Knex.

The question is how can I do this with Knex code?

I know it can be done with plain SQL (perhaps something using
GROUP BY
) but I'd specifically like to achieve this in "pure" knex without using raw SQL.

Answer

In normal sql you do it like this.

You perform a self join and try to find a row with same idFoo but bigger id, if you dont find it you have NULL. And will know you are the bigger one.

 SELECT t1.id, t1.idFoo, t1.serialNumber
 FROM foo as t1
 LEFT JOIN foo as t2
   ON t1.id < t2.id
  AND t1.idFoo = t2.idFoo  
 WHERE t2.idFoo IS NULL

So check for left join on knex.js

EDIT:

Just check documentation build this (not tested):

 knex.select('t1.*')
     .from('foo as t1')
     .leftJoin('foo as t2', function() {
        this.on('t1.id', '<', 't2.id')
            .andOn('t1.idFoo ', '=', 't2.idFoo')
        })
     .whereNull("t2.idFoo")