Mysql: Select N Rows, But with Only Unique Values in One Column

MySQL: Select N rows, but with only unique values in one column

Probably not the most elegant of solutions, and the performance of IN may suffer on larger tables.

The nested query gets the minimum Birthyear for each city. Only records who have this Birthyear are matched in the outer query. Ordering by age then limiting to 3 results gets you the 3 oldest people who are also the oldest in their city (Egon Spengler drops out..)

SELECT Name, City, Birthyear, COUNT(*) AS ct
FROM table
WHERE Birthyear IN (SELECT MIN(Birthyear)
               FROM table
               GROUP by City)
GROUP BY City
ORDER BY Birthyear DESC LIMIT 3;

+-----------------+-------------+------+----+
| name            | city        | year | ct |
+-----------------+-------------+------+----+
| Henry Jones     | Chicago     | 1899 | 1  |
| Mac Taylor      | New York    | 1955 | 1  |
| Sarah Connor    | Los Angeles | 1959 | 1  |
+-----------------+-------------+------+----+

Edit - added GROUP BY City to outer query, as people with same birth years would return multiple values. Grouping on the outer query ensures that only one result will be returned per city, if more than one person has that minimum Birthyear. The ct column will show if more than one person exists in the city with that Birthyear

Select rows where top n values of a certain column are distinct

In the following query I INNER JOIN your main table to a temporary table which obtains the (up to) 10 highest postid values. This allows you to retain only records in your original table which have one of the 10 highest postid values. The records you don't want are filtered out since they will not match to anything in the temporary table.

SELECT t1.sequence, t1.postid, t1.text
FROM table t1 INNER JOIN
(
    SELECT DISTINCT postid
    FROM table
    ORDER BY postid DESC
    LIMIT 10
) t2
ON t1.postid = t2.postid

Mysql - Select only rows where column has unique value

SELECT 
    ID, 
    SATTELLITE_ID, 
    ATT_TYPE_ID, 
    TIME, 
    ROLL, 
    PITCH, 
    YAW 
FROM Table 
GROUP BY 
    SATTELLITE_ID, 
    ATT_TYPE_ID, 
    TIME, 
    ROLL, 
    PITCH, 
    YAW 
HAVING COUNT(*) = 1

Select Unique Rows Based on Single Distinct Column - MySQL

If you are using MySQL 5.7 or earlier, then you may join your table to a subquery which finds the most recent record for each email:

SELECT t1.id, t1.title, t1.email, t1.commentname
FROM yourTable t1
INNER JOIN
(
    SELECT email, MAX(id) AS latest_id
    FROM yourTable
    GROUP BY email
) t2
    ON t1.email = t2.email AND t1.id = t2.latest_id;

If you are using MySQL 8+, then just use ROW_NUMBER here:

WITH cte AS (
    SELECT id, title, email, commentname,
        ROW_NUMBER() OVER (PARTITION BY email ORDER BY id DESC) rn
    FROM yourTable
)

SELECT id, title, email, commentname
FROM cte
WHERE rn = 1;

Note: Your expected output probably has a problem, and the id = 6 record is the latest for rob@hotmail.com.

Select rows that contain the first N unique values in a certain column

You can use DENSE_RANK to achieve your required output as below-

DEMO HERE

 SELECT * FROM 
 (
      SELECT *,
      DENSE_RANK() OVER(ORDER BY [X1]) RN
      FROM your_table
 )A
 WHERE RN <= 3

MySQL: Select rows that have only unique values except for a column

You can apply the max() aggregate to the ID column and then GROUP BY the rest:

select max(id) id, SATELLITE_ID, ATT_TYPE_ID, TIME, Roll, Pitch, yaw
from attitude
group by SATELLITE_ID, ATT_TYPE_ID, TIME, Roll, Pitch, yaw
order by id

See SQL Fiddle with Demo

Result:

| ID | SATELLITE_ID | ATT_TYPE_ID | TIME | ROLL | PITCH | YAW |
---------------------------------------------------------------
|  2 |            1 |           1 | 2012 |    1 |     2 |   1 |
|  3 |            1 |           1 | 2011 |    1 |     2 |   1 |

mysql: select * from table but with unique value in specific column

You are using DISTINCT in wrong way; it is not a function. Following query would get unique combinations of userid, date1, date2

SELECT DISTINCT 
  userid, 
  date1, 
  date2 
FROM orders 

Mysql SELECT only unique values in one column when left joined with another table

From the specs you gave, all you have to do is group by ID and username, then pick the lowest value of relation you can find (since C < I < N)

SELECT a.id, a.userName, MIN(if(o.userId=1,'C',if(i.userId=1,'I','N'))) AS relation 
    FROM tbl_users AS a 
    LEFT JOIN tbl_contacts AS o ON a.id = o.contactId 
    LEFT JOIN tbl_invites AS i ON a.id = i.invitedId 
GROUP BY a.id, a.username

mysql select distinct n row that has a certain value

I think that this will do what you want:

select t1.*
from tablename t1 
inner join (
  select t1.id id1, t2.id id2
  from tablename t1 inner join tablename t2
  on t2.id > t1.id 
  and ('red' in (t1.name, t2.name)) + ('3' in (t1.number, t2.number)) = 2
  order by rand() limit 1
) t2 on t1.id in (t2.id1, t2.id2)

Note that the row with the highest probability to be returned is id = 2, because it can be combined with any other row of the table.

See the demo.

---

Related Topics