DISTINCT Rows

Multi-column DISTINCT Keys

Every selected column participates in the key.

highlighted = computed this step

Multi-column DISTINCT keys

With several selected columns, the whole projected row is the key.

whole projected row\text{whole projected row}

Read multi-column keys

The key uses 2 columns and produces 4 distinct rows.

distinct rows=4\text{distinct rows}=4

DISTINCT examples are tiny finite bag-to-table transforms; this model shows first-seen representatives for deterministic display and does not claim product SQL output ordering.

Multi-column DISTINCT keys: bag input rowsidcitystatus1Austinopen2Austinopen3Bostonclosed4Austinclosed5NULLopen6NULLopen WHERE then SELECT projectionsourceinputwhereTruthprojectedRowdistinctRole0[1, Austin, open]TRUE[Austin, open]representative1[2, Austin, open]TRUE[Austin, open]duplicate2[3, Boston, closed]TRUE[Boston, closed]representative3[4, Austin, closed]TRUE[Austin, closed]representative4[5, NULL, open]TRUE[NULL, open]representative5[6, NULL, open]TRUE[NULL, open]duplicate DISTINCT groupskeysourcesrepresentativeSourceduplicateCount[Austin, open][0, 1]01[Boston, closed][2]20[Austin, closed][3]30[NULL, open][4, 5]41 DISTINCT output rowscitystatusAustinopenBostonclosedAustinclosedNULLopen DISTINCT factsfactvalueinputRowCount6whereKeptRows6whereDroppedRows0projectedRowCount6distinctRowCount4duplicateDropCount2nullKeyRows2selectColumnCount2representativeOrderfirst_seen_model_order_not_sql_order_claim

City plus status

Austin with open and Austin with closed are different projected rows.

both columns matter\text{both columns matter}

Summary

DISTINCT compares every selected column together.

full key comparison\text{full key comparison}