Yahoo Answers is shutting down on May 4th, 2021 (Eastern Time) and beginning April 20th, 2021 (Eastern Time) the Yahoo Answers website will be in read-only mode. There will be no changes to other Yahoo properties or services, or your Yahoo account. You can find more information about the Yahoo Answers shutdown and how to download your data on this help page.

Trending News

Promoted
i t
Lv 4
i t asked in Computers & InternetProgramming & Design · 8 years ago

How do I show unique values in SQL?

Okay, so I'm having a bit of trouble finding the answer to this. If for example I have a table that has:

ProductName and DiscountPercent, how do I show the items that do not have duplicates? I don't want to remove duplicates and show those AT ALL. Say If I have a guitar, keyboard, etc etc, and I had discount percents of 1, 2, 15, 30, 1, 2, 15...how could I show the unique value of only 30 with its matching product?

Update:

also, i'm not looking for a where discountpercent =30 either...i need to find them without knowing the unique values before hand.

Update 2:

Negative. I'm not looking for a max. I'm looking for a unique value that doesn't have duplicates. Another example would be 1,1,2,3,4,5,3,4,5 I'm looking for the 2 in this case. another example would be 5,5,6,6,7,1,2,3 i'd be looking for the 7,1,2, and 3.

Update 3:

that seems right, i'm not sure how to implement it as it's already throwing errors, but it makes sense, thanks for giving me something to work with.

Update 4:

Chris, that seems like it should work, that's what I've been trying but it keeps throwing an error:

Column 'Products.ProductName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Any idea why?

Update 5:

I'm using Microsoft SQL Server 2012

Update 6:

believe it or not that's still showing non unique percentages Serge

Update 7:

ah ha. thanks serge. i see what you did there. you rock.

3 Answers

Relevance
  • Serge M
    Lv 6
    8 years ago
    Favorite Answer

    select ProductName, DiscountPercent

    from table

    where ProductName IN

    (select ProductName from table

    group by ProductName having count(*) = 1)

    Source(s): http://www.sql-tutorial.ru/en/book_group_by_clause...
  • Chris D
    Lv 6
    8 years ago

    Something like this works for me

    SELECT ProductName, DiscountPercent

    FROM Products

    GROUP BY DiscountPercent

    HAVING COUNT(DiscountPercent) = 1

    sqlite3 t.sql3

    SQLite version 3.7.13 2012-06-11 02:05:22

    Enter ".help" for instructions

    Enter SQL statements terminated with a ";"

    sqlite> create table Products (ProductName char(30), DiscountPercent integer);

    sqlite> insert into Products values ('guitar', 1);

    sqlite> insert into Products values ('guitar', 15);

    sqlite> insert into Products values ('guitar', 30);

    sqlite> insert into Products values ('keyboard', 1);

    sqlite> insert into Products values ('keyboard', 15);

    sqlite> insert into Products values ('drums', 25);

    sqlite> select ProductName, DiscountPercent from Products group by DiscountPercent having count(DiscountPercent) = 1;

    drums|25

    guitar|30

    ----------

    Additional Details response: What SQL database system are you using? Oracle is really picky about its GROUP BY clauses, and SQL Server is just plain non-standard.

  • markhorb
    8 years ago

    So you are looking for the max value of DiscountPercent. Use the max(fieldname) function like:

    select ProductName, max(DiscountPercent)...

Still have questions? Get your answers by asking now.