Subscribe Now: Feed Icon

Sunday, March 4, 2012

Selecting Distinct Rows

Or how the definition of the request can get us confused?

One of my team members asked me today to help her in a SQL query and although I hate SQL I tried to help her. She wanted to select a row by a distinct field – ColumnA and to view other columns in that row, so I started the query with the distinct keyword:

  1. SELECT DISTINCT ColumnA
  2. FROM SomeTable

But run into the problem that she wanted those other columns of the row which weren’t distinct.

So I Googled Distinct and got lost. After taking a coffee break I changed my search to “sql select id of first distinct” the first link was of StackOverFlow: SQL SELECT DISTINCT ID from copies ORDER BY CID

Which told me of course that I was using the wrong keyword and should have used the Group by keyword.

The end result query:

  1. SELECT *
  2. FROM SomeTable
  3. WHERE OBJECTID IN ( SELECT MIN(OBJECTID)
  4.                     FROM SomeTable
  5.                     GROUP BY ColumnA)

Was what she wanted.

God bless coffee (and StackOverFlow)…

Resources:

StackOverFlow - SQL SELECT DISTINCT ID from copies ORDER BY CID