MySQL: SELECT DISTINCT multiple columns

Question

My SQL query looks like this:

SELECT DISTINCT ip, title, url
FROM stats;

My goal is to select one row for each distinct ip, along with title and url; however I find when I add the title and url fields to my query it shows me all rows.

Sample Data

ip          title      url
---------------------------
127.0.0.1   title      url
127.0.0.2   title      url
127.0.0.1   difftitle  url

Result I would like

ip          title      url
---------------------------
127.0.0.1   title      url
127.0.0.2   title      url

Not quite clear provide some sample data and expected result out of it into the question. — Abhik Chakraborty
– Abhik Chakraborty, Commented Apr 17, 2015 at 13:16
if you have two different title or url with the same IP, which of them you want select? — vathek
– vathek, Commented Apr 17, 2015 at 13:24
@NotaGuruAtAll The first occurence? Apart from the fact, that there's no "first" in a relational database, unless you define it with another column, this is very confusing. I thought, you want the rows where ip is unique? What's the desired result from your sample data? — fancyPants
– fancyPants, Commented Apr 17, 2015 at 13:34
So you only want one title and url per ip? What determines which title and url? Or do you not care which one is shown? — Eilidh
– Eilidh, Commented Apr 17, 2015 at 14:52

Eilidh · Accepted Answer · 2015-04-17 14:58:58Z

5

I think what you are looking for is a query like this -

SELECT 
    ip,
    title,
    url
FROM 
    stats
GROUP BY 
    ip

GROUP BY is similar to DISTINCT - it means that all results will be grouped by ip, so it will only show one row of results for each distinct ip. However, nothing determines which record will be returned (e.g. which title and url will be shown).

There is no 'first' entry in the database - a relational database has no 'order' as such, unless you choose to order by a field.

answered Apr 17, 2015 at 14:58

Eilidh

1,2981 gold badge13 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

donstack Over a year ago

title, url needs to go in Group by as well or needs to be put in some aggregate function like max() in Select

Collectives™ on Stack Overflow

MySQL: SELECT DISTINCT multiple columns

Sample Data

Result I would like

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Sample Data

Result I would like

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related