MySQL - SELECT all columns WHERE one column is DISTINCT

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).

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.

> 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)

SELECT Id, Link, Day, Month FROM Posted
WHERE Id IN(
   SELECT Min(Id) FROM Posted GROUP BY Link)

If what your asking is to only show rows that have 1 link for them then you can use the following:

SELECT * FROM posted WHERE link NOT IN 
(SELECT link FROM posted GROUP BY link HAVING COUNT(LINK) > 1)

Again this is assuming that you want to cut out anything that has a duplicate link.

I think the best solution would be to do a subquery and then join that to the table. The sub query would return the primary key of the table. Here is an example:

select *
from (
          SELECT row_number() over(partition by link order by day, month) row_id
          , *

          FROM posted 
          WHERE ad='$key' 
          ) x
where x.row_id = 1

What this does is the row_number function puts a numerical sequence partitioned by each distinct link that results in the query.

By taking only those row_numbers that = 1, then you only return 1 row for each link.

The way you change what link gets marked "1" is through the order-by clause in the row_number function.

Hope this helps.

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

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

OR

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

If you want all columns where link is unique:

SELECT * FROM posted WHERE link in
     (SELECT link FROM posted WHERE ad='$key' GROUP BY link);

What you want is the following:

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

if there are 4 rows for example where link is the same, it will pick only one (I asume the first one).

I had a similar problem, maybe that help someone, for example - table with 3 columns

SELECT * FROM DataTable WHERE Data_text = 'test' GROUP BY Data_Name ORDER BY Data_Name ASC

or

SELECT Data_Id, Data_Text, Data_Name FROM DataTable WHERE Data_text = 'test' GROUP BY Data_Name ORDER BY Data_Name ASC

Two ways work for me.

In MySQL you can simply use "group by". Below will select ALL, with a DISTINCT "col"

SELECT *
FROM tbl
GROUP BY col

SELECT a.* FROM orders a INNER JOIN (SELECT course,MAX(id) as id FROM orders WHERE admission_id=".$id." GROUP BY course ) AS b ON a.course = b.course AND a.id = b.id

With the Above Query you will get unique records with where condition

Select the datecolumn of month so that u can get only one row per link, e.g.:

select link, min(datecolumn) from posted WHERE ad='$key' ORDER BY day, month

Good luck............

Or

u if you have date column as timestamp convert the format to date and perform distinct on link so that you can get distinct link values based on date instead datetime