MySQL - SELECT all columns WHERE one column is DISTINCT

Question

I'm very sorry if the question seems too basic.
I've surfed entire Internet and StackOverflow for a finished solution, and did not find anything that I can understand, and can't write it myself, so have to ask it here.

I have a MySQL database.
It has a table named "posted".
It has 8 columns.

I need to output this result:

SELECT DISTINCT link FROM posted WHERE ad='$key' ORDER BY day, month 

But I need not only the "link" column, but also other columns for this row.
Like for every row returned with this query I also need to know its "id" in the table, "day" and "month" values etc.

Please tell me what should I read to make it, or how to make it.
Please keep it as simple as possible, as I'm not an expert in MySQL.

Edit: I tried this:

SELECT DISTINCT link,id,day,month FROM posted WHERE ad='$key' ORDER BY day, month 

It doesn't work. It returns too many rows. Say there are 10 rows with same links, but different day/month/id. This script will return all 10, and I want only the first one (for this link).

Answer 1

I tried this:

SELECT DISTINCT link,id,day,month FROM posted      WHERE ad='$key' ORDER BY day, month 

It doesn't work. It returns too many rows. Say there are 10 rows with same links, but different day/month/id. This script will return all 10, and I want only the first one (for this link).

What you're asking doesn't make sense.

Either you want the distinct value of all of link, id, day, month, or you need to find a criterion to choose which of the values of id, day, month you want to use, if you just want at most one distinct value of link.

Otherwise, what you're after is similar to MySQL's hidden columns in GROUP BY/HAVING statements, which is non-standard SQL, and can actually be quite confusing.

You could in fact use a GROUP BY link if it made sense to pick any row for a given link value.

Alternatively, you could use a sub-select to pick the row with the minimal id for a each link value (as described in this answer):

 SELECT link, id, day, month FROM posted      WHERE (link, id) IN            (SELECT link, MIN(id) FROM posted ad='$key' GROUP BY link) 

Answer 2

The problem comes from instinctively believing that DISTINCT is a local pre-modifier for a column.

Hence, you "should" be able to type

XXbadXX SELECT col1, DISTINCT col2 FROM mytable XXbadXX

and have it return unique values for col2. Sadly, no. DISTINCT is actually a global post-modifier for SELECT, that is, as opposed to SELECT ALL (returning all answers) it is SELECT DISTINCT (returning all unique answers). So a single DISTINCT acts on ALL the columns that you give it.

This makes it real hard to use DISTINCT on a single column, while getting the other columns, without doing major extremely ugly backflips.

The correct answer is to use a GROUP BY on the columns that you want to have unique answers: SELECT col1, col2 FROM mytable GROUP BY col2 will give you arbitrary unique col2 rows, with their col1 data as well.