Using Distinct in SQL query

Please find the query given below:

SELECT DISTINCT 
        reco_index_content_code,
        reco_index_content_name,
        reco_index_content_url
    FROM tbl_reco_index_contents
    WHERE
        reco_index_user_action_at_select = 1
        AND user_profile_number = 1

Open in new window


I need to select reco_index_content_name as distinct. How should the above query be modified in order to accomplish that, such that there are no duplicate 'reco_index_content_name' rows

Any help which would lead me to the correct solution would be well appreciated with points
Sreejith22Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SELECT DISTINCT does a distinct for all columns in the select query, not just one.

So the question you'll need to answer is ... For a given reco_index_content_name value, if there are three rows that have three different reco_index_content_code or ..._url values, which one do you want to display?  First, last, don't display them, ...
0
Sreejith22Author Commented:
this was a mistake done by one developer..he did multiple entries for same values with different reco_index_content_code. So, I need to fetch only one row, irrespective of what the reco_index_content_code is; and I would not loose any data if I exclude the other rows, data being same in other rows as I told
0
Sreejith22Author Commented:
any updates? or any doubts ?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>irrespective of what the reco_index_content_code is
Okay, in that case let's just grab the min values from the other columns
SELECT  
	reco_index_content_code,
	Min(reco_index_content_name) as reco_index_content_name,
	Min(reco_index_content_url) as reco_index_content_name
FROM tbl_reco_index_contents
WHERE reco_index_user_action_at_select = 1 AND user_profile_number = 1 
GROUP BY reco_index_content_code

Open in new window

0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Correction

>irrespective of what the reco_index_content_code is
Okay, in that case let's just grab the min values from the other columns
SELECT  
	reco_index_content_name,
	Min(reco_index_content_code) as reco_index_content_code,
	Min(reco_index_content_url) as reco_index_content_url
FROM tbl_reco_index_contents
WHERE reco_index_user_action_at_select = 1 AND user_profile_number = 1 
GROUP BY reco_index_content_name

Open in new window

0
PortletPaulfreelancerCommented:
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you are getting still have “duplicates” then you are expecting something else that SELECT DISTINCT does not do. In truth, “select distinct” is a rather dull animal with minimal decision making powers.

The most common solution to this “I still got duplicates” is to use GROUP BY instead, but please read on. (or, cheat and click this.)
The first thing to recognize about SELECT DISTINCT is that overall it’s a bit boring. It’s isn’t a function so if you are using a query like this:

SELECT DISTINCT (column1), column2, ... column40
You will be disappointed to learn that those parentheses around column1 do NOT work to “make column1 unique, then by magic decide how to simplify the other columns”.  

And: With or without the parentheses, SELECT DISTINCT does not know that you really want it to concentrate on column1 or (column1 and column2) ignoring the other columns.

SELECT DISTINCT is a “row operator”, not a function, and not magic. It will evaluate ALL of the columns that you have listed and remove rows that have exactly the same values in all columns so that each row returned is different to every other row that is returned. That might mean for example that a value containing an exclamation in column27 and a value that does not have an exclamation in column27 causes 2 rows to be returned even if every other column has the same values.
col1 col2 col3 col4 col5 col6 col7 col8 col9 cl10 cl11 cl12 ... col27
aaaa bbbb cccc dddd eeee ffff gggg hhhh iiii jjjj kkkk llll ... blah! <<I'm unique!
aaaa bbbb cccc dddd eeee ffff gggg hhhh iiii jjjj kkkk llll ... blah  << me too!

Open in new window

Small note: I personally would not describe this as “having duplicates”, I prefer to describe this as “unwanted repetition”.

So, what to do?

First step:  Concentrate on the columns that MUST be unique, e.g.
select
column1 --<< MUST be unique columns  here
from atable
group by
column1 --<< MUST be unique columns  here
Second step: Decide what can be done with the other columns,
e.g. accept the maximum values of each
select
column1
, MAX(column2), MAX(column3), ...  MAX(column27) ...
from atable
group by
column1 --<< MUST be unique columns  here

Note: A query using GROUP BY is not nearly as boring as poor old “SELECT DISTINCT” and GROUP BY does offer quite a number of interesting options such as MAX(), MIN(), AVERAGE() etc.
ps: this is part of an unpublished article about using DISTINCT
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.