How to use distinct all column of table in SQL Server

Question

I want to get unique value form table. But all values should be unique.

So suggest how to get.

SELECT DISTINCT ProCode
           , id,SubCat
           ,SmlImgPath
           ,RupPrice
           ,ActualPrice
           ,ProName
 FROM product
 WHERE ProCode='FZ10003-EBA';

`DISTINCT` always applies to **all column values** - not sure what you mean .... — marc_s, Sep 02 '14 at 11:07
I think that nobody so far has an idea of what you are talking about. Please show sample data and desired output. — Thorsten Kettner, Sep 02 '14 at 11:11
From the data pasted below you appear to have the same product code in multiple sub categories. You have poorly designed tables that do not conform to normalisation. — CaRDiaK, Sep 02 '14 at 11:23
This thread discusses the same question and has answer too http://stackoverflow.com/questions/8273987/select-unique-rows-based-on-single-distinct-column — radar, Sep 02 '14 at 11:35

Klas Lindbäck · Answer 1 · 2014-09-03T07:23:05.523

0

DISTINCT refers to all selected columns, so the answer is that your SELECT already does that.

EDIT:

It seems your problem isn't related to DISTINCT. What you want is to get a single row when your search returns multiple rows.

If you don't care which row you get then you can use:

MS SQL Server syntax:

SELECT TOP 1  ProCode
           , id,SubCat
           ,SmlImgPath
           ,RupPrice
           ,ActualPrice
           ,ProName
 FROM product
 WHERE ProCode='FZ10003-EBA';

MYSQL syntax:

SELECT ProCode
           , id,SubCat
           ,SmlImgPath
           ,RupPrice
           ,ActualPrice
           ,ProName
 FROM product
 WHERE ProCode='FZ10003-EBA'
 LIMIT 1;

Oracle syntax:

SELECT ProCode
           , id,SubCat
           ,SmlImgPath
           ,RupPrice
           ,ActualPrice
           ,ProName
 FROM product
 WHERE ProCode='FZ10003-EBA'
   AND rownum <= 1;

edited Sep 03 '14 at 07:23

answered Sep 02 '14 at 11:07

Klas Lindbäck

33,105
5
57
82

Please provide some rows from your table – Ullas Sep 02 '14 at 11:08
FZ10003-EBA ~/Products/CELEBRITY/KANGANA RANAUT/FZ10003-EBA-sm.jpg FZ10003-EBA ~/Products/SALWAR KAMEEZ/DESIGNER WEAR/FZ10003-EBA-sm.jpg FZ10003-EBA ~/Products/SALWAR KAMEEZ/PARTY WEAR/FZ10003-EBA-sm.jpg – Sandeep Pal Sep 02 '14 at 11:08
1

Add this in your question – Ullas Sep 02 '14 at 11:09
If you want a single value, then DISTINCT is not what you want. You need to search for a unique value, that is you need to have a where clause that includes all columns in a unique index. – Klas Lindbäck Sep 02 '14 at 11:36

score 0 · Answer 2 · answered Sep 02 '14 at 11:56

(one day I may be able to post comments!)

SQLFiddle to show normal, distinct and returning a single row

SELECT DISTINCT works fine but it doesn't work the way you want it to work. From the data you posted in the comment under Klas' answer, it's clear you're expecting a single result when there are data differences somewhere in the columns. For example

    /Products/CELEBRITY/KANGANA

is completely DISTINCT from

    /Products/SALWAR

What you appear to be looking for cannot work with DISTINCT nor can it work with GROUP BY. Basically the only way two (or three, or ten, or 100) rows will become ONE row is if the data in ALL SEVEN COLUMNS in your SELECT are IDENTICAL.

Take a step back and think about what it is, exactly, you're trying to achieve here.

score 0 · Answer 3 · answered Sep 02 '14 at 12:34

Are you saying that you want one record only? This is called aggregation. In case there are more records then one (three in your example), you would have to decide for each column, which value to show.

Which SubCat, which SmlImgPath, etc. do you want to see in your result line? The maximum value? The minimum? Or the string 'various'? An example:

SELECT 
    ProCode
  , CASE WHEN MIN(id) <> MAX(id) THEN 'various' ELSE MIN(id) END
  , MIN(SubCat)
  , MAX(SmlImgPath)
  , AVG(RupPrice)
  , AVG(ActualPrice)
  , MAX(ProName)
FROM product
WHERE ProCode='FZ10003-EBA'
GROUP BY ProCode;

How to use distinct all column of table in SQL Server

3 Answers3