SELECT DISTINCT on one column, while returning the other columns?

Question

I have a query which uses three lookup tables to get all the information I need. I need to have DISTINCT values for one column, however I also need the rest of the data associated with it.

My SQL code:

SELECT acss_lookup.ID AS acss_lookupID,
   acss_lookup.product_lookupID AS acssproduct_lookupID,
   acss_lookup.region_lookupID AS acssregion_lookupID,
   acss_lookup.document_lookupID AS acssdocument_lookupID,
   product.ID AS product_ID,
   product.parent_productID AS productparent_product_ID,
   product.label AS product_label,
   product.displayheading AS product_displayheading,
   product.displayorder AS product_displayorder,
   product.display AS product_display,
   product.ignorenewupdate AS product_ignorenewupdate,
   product.directlink AS product_directlink,
   product.directlinkURL AS product_directlinkURL,
   product.shortdescription AS product_shortdescription,
   product.logo AS product_logo,
   product.thumbnail AS product_thumbnail,
   product.content AS product_content,
   product.pdf AS product_pdf,
   product.language_lookupID AS product_language_lookupID,
   document.ID AS document_ID,
   document.shortdescription AS document_shortdescription,
   document.language_lookupID AS document_language_lookupID,
   document.document_note AS document_document_note,
   document.displayheading AS document_displayheading
FROM acss_lookup
     INNER JOIN product ON (acss_lookup.product_lookupID = product.ID)
     INNER JOIN document ON (acss_lookup.document_lookupID = document.ID)
ORDER BY product_displayheading ASC;

I want to get all the products from this query but I only want to get them once because I'm populating a drop down menu for a search application. I want the user to be able to select from the products that are in that table (that's why I only need them once).

Is this too complicated? Should I use a more simplified approach?

But a product is related to many documents. And your query returns them all (documents for a product). Which one should be chosen? — ypercubeᵀᴹ, Aug 08 '12 at 17:29

score 7 · Accepted Answer · answered Aug 08 '12 at 19:08

One more approach not mentioned yet is to use window functions, for instance row_number:

   SELECT * FROM  
   (
   SELECT acss_lookup.ID AS acss_lookupID, 
   ROW_NUMBER() OVER 
   (PARTITION BY your_distinct_column ORDER BY any_column_you_think_is_appropriate)
   as num,
   acss_lookup.product_lookupID AS acssproduct_lookupID,
   acss_lookup.region_lookupID AS acssregion_lookupID,
   acss_lookup.document_lookupID AS acssdocument_lookupID,
   product.ID AS product_ID,
   product.parent_productID AS productparent_product_ID,
   product.label AS product_label,
   product.displayheading AS product_displayheading,
   product.displayorder AS product_displayorder,
   product.display AS product_display,
   product.ignorenewupdate AS product_ignorenewupdate,
   product.directlink AS product_directlink,
   product.directlinkURL AS product_directlinkURL,
   product.shortdescription AS product_shortdescription,
   product.logo AS product_logo,
   product.thumbnail AS product_thumbnail,
   product.content AS product_content,
   product.pdf AS product_pdf,
   product.language_lookupID AS product_language_lookupID,
   document.ID AS document_ID,
   document.shortdescription AS document_shortdescription,
   document.language_lookupID AS document_language_lookupID,
   document.document_note AS document_document_note,
   document.displayheading AS document_displayheading
   FROM acss_lookup
     INNER JOIN product ON (acss_lookup.product_lookupID = product.ID)
     INNER JOIN document ON (acss_lookup.document_lookupID = document.ID)
   )a
   WHERE a.num = 1
   ORDER BY product_displayheading ASC;

@a1ex07- Thanks! That worked. Every time I tried to adapt some example from the net, it was my JOINS that got me confused, but I think I get it now. — stephmoreland, Aug 08 '12 at 19:31
It would be better to do the joins outside the subquery if not what makes the data "distinct", as to minimize the data you query and duplicate only to "throw away" by selecting num = 1 from the window function. — Allan S. Hansen, Nov 13 '13 at 14:51

score 4 · Answer 2 · answered Aug 08 '12 at 17:36

4

There are a few ways of doing this. The two main ones I use are common table expressions and sub-queries. Using a CTE your query would look something like this:

WITH theResultSet AS
(
    SELECT DISTINCT(column) AS col1 FROM some.table
)
SELECT whatever
  FROM more.data AS a
  JOIN theResultSet as b ON a.col1 = b.col1
  /* additional joins, clauses etc...*/

Or using a subquery:

SELECT whatever
  FROM more.data AS a
  JOIN (SELECT DISTINCT(column) AS col1 FROM some.table) AS b ON a.col1 = b.col1
/* additional joins, clauses etc... */

I normally test to see which is faster and go with that one.

I hope this helps you.

answered Aug 08 '12 at 17:36

Mr.Brownstone

13,102
4
36
54

I thought I understood your answer so I tried it (the first one), but I think my JOINS are causing a problem with your solution's JOINS. – stephmoreland Aug 08 '12 at 17:56
what is the column that needs to be distinct? I will post a more comprehensive solution for you. – Mr.Brownstone Aug 08 '12 at 18:00
product.displayheading is the column – stephmoreland Aug 08 '12 at 19:02

score 1 · Answer 3 · answered Aug 08 '12 at 17:56

^{(I think what you're trying to do is "collapse" each result row to a single product, so this answer is going on that assumption.)}

This is not possible. To get the related 1..* data from the other tables, you have to return duplicate values in the other columns.

Generally the way to handle this is to run the query as it is, and process the joined result set in application code. I usually do this using a hash collection approach that ends up with distinct entities of each type in a collection based on a key value.

While this approach does cost more in terms of network traffic, it's usually preferable to doing something like running multiple queries and stitching together the results as you need in the application code. It depends on many factors, including how frequently the query/queries run and how much data is returned.

SELECT DISTINCT on one column, while returning the other columns?

3 Answers3