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

User · Answer

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.

User · Answer

In MySQL you can simply use  quot group by quot   Below will select ALL  with a DISTINCT  quot col quot  SELECT   FROM tbl GROUP BY col

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT OTHER COLUMNS FROM posted WHERE link in     SELECT DISTINCT link FROM posted WHERE ad   key      ORDER BY day  month

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   gt  1    Again this is assuming that you want to cut out anything that has a duplicate link

User · Answer

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

User · Answer

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

[mysql] MySQL - SELECT all columns WHERE one column is DISTINCT

Examples related to mysql

Examples related to sql