SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Guru
    Join Date
    Jun 2004
    Location
    UK
    Posts
    605
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    SELECT all data WHERE one column is DISTINCT

    Hi, maybe someone can help...

    I have a database with the following headings:

    The database of products contains the following headings:

    Id, Title, Description, Manufacturer, Price

    Some of the products have the same title but different manufacturer. Basically, I need to return all of the data for a product directory, but only where titles are unique (the title can then be clicked to find the different manufacturers for this title). The results have to be ordered by title, and where there is more than one similar title, the results must show the product of minimum price - so that I can put "Prices start at...".

    Just to reiterate - all data needs to be returned except where it contains duplicate Title values.

    Does this make sense, and can anyone help?? Thanks.

  2. #2
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    try
    select title, min(price)
    from tablename
    group by title

  3. #3
    SitePoint Wizard
    Join Date
    Jul 2003
    Location
    Kent
    Posts
    1,921
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    or perhaps even

    select title, min(price) count(*)
    from tablename
    group by title
    order by title

    this way you could say how many models were available.

    both queries will give a list of titles, minimum price and the second adds the number of models. In a group by youcan't get other info unless it is in the group by list, which then results in other groups being created

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    makes perfect sense, a very common requirement
    Code:
    select Id
         , Title
         , Description
         , Manufacturer
         , Price
      from yourtable XX
     where Price
         = ( select min(Price)
               from yourtable
              where Title = XX.Title )
    order
        by Title
    this uses a correlated subquery which has the same effect as grouping
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Evangelist IJoeR's Avatar
    Join Date
    Feb 2003
    Location
    Somewhere in, MD
    Posts
    400
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what are the "XX" - what do they mean?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it's a table alias

    it is necessary in order to distinguish the two "copies" of the table

    it is called the correlation variable, because it correlates each row in the outer query with all the other rows (in the subquery) that have the same Title
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •