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

I don't quite understand what you want to accomplish. If you `DISTINCT link`, value of other columns are vague. You can't just select other columns. Can you explain a little bit more? — mask8, Jul 25 '12 at 00:59
You CAN just select other vague columns if you want a single, arbitrary entry having that description. Example: Pick a lottery winner; pull a processing entry from a pool; etc. OP is precise. — DragonLord, Nov 29 '12 at 02:30

score 106 · Answer 1 · edited Apr 04 '13 at 16:48

106

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.

edited Apr 04 '13 at 16:48

Troy Alford

26,660
10
64
82

answered Nov 29 '12 at 02:20

DragonLord

6,395
4
36
38

1

How do you determine whether you get the first row of the column that is unique or the last row of the column that is unique? – CMCDragonkai Feb 07 '15 at 07:03
1

@CMCDragonkai: Please kindly read the manual. SELECT returns ALL rows that match its query. Order can be random and is never guaranteed; use an ORDER BY clause if you need otherwise. See [http://stackoverflow.com/questions/1793147/sql-best-practice-to-deal-with-default-sort-order](http://stackoverflow.com/questions/1793147/sql-best-practice-to-deal-with-default-sort-order). Good luck. – DragonLord Feb 10 '15 at 05:34
1

so unclear to give such prominence to the wrong way to do it then to stick the correct answer within a paragraph at the bottom. – barlop Oct 22 '16 at 18:23

score 10 · Answer 2 · edited May 23 '17 at 12:10

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)

Awesome, man! I'm surprised there's not a better way to do such a common operation in MySQL. In Postgres, we have `DISTINCT ON (...)` that accomplishes this nicely. — J-DawG, Feb 22 '18 at 19:53

score 5 · Answer 3 · answered Jun 04 '15 at 03:26

5

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

answered Jun 04 '15 at 03:26

Sinaesthetic

11,426
28
107
176

score 3 · Answer 4 · answered Jul 25 '12 at 00:56

3

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

answered Jul 25 '12 at 00:56

arunmoezhi

3,082
6
35
54

Joe Meyer · Answer 5 · 2012-07-31T00:59:38.430

3

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.

edited Jul 31 '12 at 00:59

answered Jul 25 '12 at 02:46

Joe Meyer

4,315
20
28

score 3 · Answer 6 · edited Dec 02 '16 at 18:11

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.

score 1 · Answer 7 · answered Jul 25 '12 at 00:55

1

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

answered Jul 25 '12 at 00:55

mlishn

1,689
14
19

7

This doesn't solve his problem. It returns distinct _combinations_ of all the rows. The Asker wanted to display all columns but only distinct values of one of those columns. – cfwschmidt May 04 '15 at 16:59

Zonata · Answer 8 · 2012-07-25T02:06:05.387

1

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

edited Jul 25 '12 at 02:06

answered Jul 25 '12 at 01:10

Zonata

471
2
6
20

This returns all the rows in `posted`, not just the one's where `link` is unique. I think it's because your `GROUP BY` is cancelled by the `IN`, which returns duplicates as well. – Samuel Bushi Jun 20 '18 at 23:01

score 1 · Answer 9 · edited Oct 15 '15 at 15:45

1

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

edited Oct 15 '15 at 15:45

Rory McCrossan

331,213
40
305
339

answered Oct 15 '15 at 15:19

Failon

69
4

score 0 · Answer 10 · answered Apr 14 '14 at 13:59

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.

Robert Sinclair · Answer 11 · 2020-08-21T14:16:14.317

0

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

SELECT *
FROM tbl
GROUP BY col

edited Aug 21 '20 at 14:16

answered Feb 14 '19 at 17:57

Robert Sinclair

4,550
2
44
46

score 0 · Answer 12 · edited Mar 03 '20 at 16:39

0

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

edited Mar 03 '20 at 16:39

techie007

737
1
10
30

answered Aug 07 '19 at 12:14

abhinandan dev

1
2

1

Welcome to Stack Overflow. While your code may provide the answer to the question, please add context around it so others will have some idea what it does and why it is there. – Theo Aug 07 '19 at 12:18
1

Also, this question has already been answered very well. Why you are trying to answer a old question. OP may not have accepted answer, but has been up voted many times. I would suggest you to find new questions to improve upon skills – Ven Aug 07 '19 at 12:20

kalyan · Answer 13 · 2014-04-04T05:36:39.533

-2

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

edited Apr 04 '14 at 05:36

answered Apr 04 '14 at 04:59

kalyan

9
1

MySQL - SELECT all columns WHERE one column is DISTINCT

13 Answers13

Linked