mysql select distinct question

Discussion in 'PHP' started by Fracisc, Oct 22, 2011.

0
  1. #1
    I need something like this: select all, but x should be distinct.

    I have price, title, link. I need to have all these but the title should be distinct. How can I do that?
     
    Fracisc, Oct 22, 2011 IP
  2. blueparukia

    blueparukia Well-Known Member

    Messages:
    1,564
    Likes Received:
    71
    Best Answers:
    7
    Trophy Points:
    160
    #2
    SELECT * FROM table WHERE title='title_name'
     
    blueparukia, Oct 22, 2011 IP
  3. geforce

    geforce Active Member

    Messages:
    173
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    78
    #3
    You could try and GROUP BY instead.SELECT title, price, linkFROM tableGROUP BY title, price, link
     
    geforce, Oct 22, 2011 IP
  4. Fracisc

    Fracisc Well-Known Member

    Messages:
    3,670
    Likes Received:
    10
    Best Answers:
    1
    Trophy Points:
    195
    #4
    blueparukia, thanks but that's a good answer for another question.. :)
    Geforce, how will that select unique titles and all the rest? Can you gimme an example?
     
    Fracisc, Oct 22, 2011 IP
  5. geforce

    geforce Active Member

    Messages:
    173
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    78
    #5
    Hi,For example if you wanted to bring out unique titles and say the minimum price for that title you could do:

    SELECT title, MIN(price), link
 FROM table
 GROUP BY title, link
    Code (markup):
    That will bring out all unique title and link with the minimum price available :)
     
    geforce, Oct 22, 2011 IP
  6. blueparukia

    blueparukia Well-Known Member

    Messages:
    1,564
    Likes Received:
    71
    Best Answers:
    7
    Trophy Points:
    160
    #6
    Sorry, I may have misunderstood. I thought you wanted to select a unique row by title. Group by might work, but I'm not exactly clear on what you are asking.
     
    blueparukia, Oct 22, 2011 IP
  7. Rukbat

    Rukbat Well-Known Member

    Messages:
    2,908
    Likes Received:
    37
    Best Answers:
    51
    Trophy Points:
    125
    #7
    What you want is (if it existed) SELECT (distinct title), link, price. The problem is that if you have 2 entries for a single title, with different links or different prices, the logic is "while true == false" - IOW, title is distinct but link (or price) isn't, so a single row can't satisfy your query. In this situation, if you want non-corrupt data, you must have more than 1 row returned for the same title. Returning a single row with the title and one of the links or one of the prices is giving you a corrupt dataset.

    If the link and price are the same for all rows with the same title,

    SELECT DISTINCT title, link, price

    will do what you want.

    (You can't have a distinct non-distinct dataset - there's no such thing.)
     
    Rukbat, Oct 24, 2011 IP