How to redefine Distinct

Question

My boss has given me an assignment that I'm not sure is possible since after about two weeks, I can't figure out a solution, so I'm throwing this out to ask for any sort of help from the SO group. If this breaks your brain, I apologize.

A little background first: We develop a database querying application that allows users to get back anything they want from the database without having to know any SQL.

My Problem: We want to customize way a "Distinct" selection is used. Instead of having the distinct keyword apply to the rows selected, we want it to apply to only certain fields, so that the first rows that match the fields will get used. For an example:

Bob Jones, 122 Main Street, Portland, OR
Sue Jones, 122 Main Street, Portland, OR
Mary Smith, 458 32nd Street, Portland, OR
Ralph Smith, 458 32nd Street, Portland, OR

we want this to work so that only the first rows for each address is returned as follows:

Bob Jones, 122 Main Street, Portland, OR
Mary Smith, 458 32nd Street, Portland, OR

With the normal distinct, all four rows would be returned, which is more than we want. Does anyone have any ideas as to how we can go about doing this? Thanks.

Answer 1

You have to run a regular "SELECT DISTINCT" sql statement that only queries for the fields the actually do need to be distinct. Then once you get those back, run another query that takes the TOP 1 record that has matching data to get the non-distinct fields. At the moment I don't see a way to do this in a single SQL statement...

Answer 2

SELECT MIN (a.firstname + a.lastname) , address from x Group By address.

That's an overly simplistic answer, but it gets your point accross. it will return only the "lesser" name for each distinct address.

You'll need to use field concatenation to do the distinctions as well, but it will give you what you need.

Answer 3

WITH NumberedRows AS (
   SELECT FirstName, LastName, Address,
          ROW_NUMBER() OVER (PARTITION BY Address ORDER BY Id) as RowNumber
   FROM Table
) SELECT FirstName, LastName, Address
  FROM NumberedRows
  WHERE RowNumber = 1;

PARTITION BY Address specifies the column list you want to group by, and ORDER BY ID indicates the column list you define the "first" item with.

(Make sure the previous statement is terminated by a semicolon.)