CODEWITHSUNDEEP
sqlsql-server
cjtampa
cjtampa

Reputation: 41

Select distinct columns but include top 1 from another column in results, based on distinction

I have a product table of 6 columns. Products are of a type, and types are of a category.

I selected 4 columns where I use DISTINCT to retrieve a set of results....

SELECT DISTINCT category, type, catlong, typelong 
FROM products 
WHERE catlong = @getcatlong 
ORDER BY type

This gives me a listing of product types and their category names based on my querystringparameter.

Example...

 category            type              catlong            typelong
----------------------------------------------------------------------
Drink Cups       Big Drink Cups       drink-cups        big-drink-cups
Drink Cups       Med Drink Cups       drink-cups        med-drink-cups
Drink Cups       Small Drink Cups     drink-cups        small-drink-cups

In the same table we generated the above query from, there are also columns for productname, productpicfilename. There are several product pic filenames to each type and category and are all unique. I need rewrite the query to include the top 1 productpicfilename for each type.

Example...

 category            type              catlong            typelong         procuctfilename
--------------------------------------------------------------------------------------------
Drink Cups       Big Drink Cups       drink-cups        big-drink-cups     redsolocups.jpg
Drink Cups       Med Drink Cups       drink-cups        med-drink-cups     bluesipcups.jpg
Drink Cups       Small Drink Cups     drink-cups        small-drink-cups   pinkdixiecups.jpg

I am unsure how to approach this. I have tried multiple different types of queries but they either return undesired results or errors. I will modify the title to this page if needed. Thank you.

Upvotes: 0

Views: 903

Answers (2)

Alex
Alex

Reputation: 5165

You can use GROUP BY. See https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql

SELECT category, type, catlong, typelong, MAX( procuctfilename ) AS procuctfilename 
FROM products 
WHERE catlong = @getcatlong 
ORDER BY category, type, catlong, typelong

An alternative way is to use Window Functions:

SELECT category, type, catlong, typelong, procuctfilename
FROM
( SELECT category, type, catlong, typelong, procuctfilename,
    -- Split all rows into groups (same as GROUP BY) and calculate order within each group
    ROW_COUNT() OVER( PARTITION BY category, type, catlong, typelong ORDER BY procuctfilename ) AS TopRow
FROM products
WHERE catlong = @getcatlong ) AS TopRows
WHERE TopRow = 1 -- returns only the first row from each group
ORDER BY category, type, catlong, typelong

This query is essentially an "inline" version of the above GROUP BY query. The advantage of this approach is that all of the columns will always belong to the same row. This is valuable when, say, you want to also show productname that corresponds with procuctfilename.

Upvotes: 0

dnoeth
dnoeth

Reputation: 60502

Simply switch to aggregation instead of DISTINCT:

SELECT category, type, catlong, typelong, MIN(procuctfilename)
FROM products 
WHERE catlong = @getcatlong 
GROUP BY category, type, catlong, typelong 
ORDER BY type

Upvotes: 1

Related Questions