To Get Distinct Rows By specific columns

My purpose today is to get the rows with unique data based on criteria of uniqueness value on the column, including combinations of column.  As you may think, just use the DISTINCT clause. You are right! Let's try with PostgreSQL.

Firt, list the data using this statement:

SELECT a.nik, a.nama, g.ket as job, h.akunno, h.alokasi
FROM ester.maskar a
   INNER JOIN  ester.setnamajob g ON a.jabid=g.idku
   INNER JOIN  ester.setnamajobakun h ON g.idku=h.jobid
   WHERE  a.thn=2012 AND  a.unitid = 16 ORDER BY a.nik, a.nama
=========================================================
nik   nama          job                   akunno  alokasi
=========================================================
100.200  ALBERT    Manajer                400     0.00
100.300  JHONI     Danru Satpam           423     1.00
100.400  ZAENAL    Anggota Satpam         423     1.00
100.500  ROBI      Anggota Satpam         423     1.00
200.400  TEDDY     Operator St. Kempa     603.07  0.40
200.400  TEDDY     Operator St. Kempa     603.08  0.40
200.400  TEDDY     Operator St. Kempa     603.09  0.20
300.500  JOKO      Operator Kolam Limbah  603.11  0.50
300.500  JOKO      Operator Kolam Limbah  603.12  0.50
300.650  SEPTIAN   Operator St. Kempa     603.09  0.20
300.650  SEPTIAN   Operator St. Kempa     603.07  0.40
300.650  SEPTIAN   Operator St. Kempa     603.08  0.40
400.600  OPA OPO   Kerani I TUK           401     1.00
----------------------------------------------------------

DISTINCT 
DISTINCT will remove all duplicate rows from the result set and one row is kept from each group of duplicates. Let's try with DISTINCT clause:

SELECT DISTINCT a.nik, a.nama, g.ket as job, h.akunno, h.alokasi
FROM ester.maskar a
   INNER JOIN  ester.setnamajob g ON a.jabid=g.idku
   INNER JOIN  ester.setnamajobakun h ON g.idku=h.jobid
   WHERE  a.thn=2012 AND  a.unitid = 16 ORDER BY a.nik, a.nama

What is your result?
I've got same result set with previous!


DISTINCT ON
Now try with this command:

 SELECT DISTINCT ON (nik,nama) a.nik, a.nama, g.ket as job,h.akunno, h.alokasi
FROM  ester.maskar a
   INNER JOIN  ester.setnamajob g ON a.jabid=g.idku
   INNER JOIN  ester.setnamajobakun h ON g.idku=h.jobid
   WHERE  a.thn=2012 AND  a.unitid = 16 ORDER BY a.nik, a.nama

=========================================================
nik   nama          job                   akunno  alokasi
=========================================================
100.200  ALBERT    Manajer                400     0.00
100.300  JHONI     Danru Satpam           423     1.00
100.400  ZAENAL    Anggota Satpam         423     1.00
100.500  ROBI      Anggota Satpam         423     1.00
200.400  TEDDY     Operator St. Kempa     603.07  0.40
300.500  JOKO      Operator Kolam Limbah  603.11  0.50
300.650  SEPTIAN   Operator St. Kempa     603.09  0.20
400.600  OPA OPO   Kerani I TUK           401     1.00
---------------------------------------------------------
Those are the result set that I want.

DISTINCT ON ( expression [, ...] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. The DISTINCT ON expressions are interpreted using the same rules as for ORDER BY. Note that the "first row" of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first.

The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s). The ORDER BY clause will normally contain additional expression(s) that determine the desired precedence of rows within each DISTINCT ON group.