Using SELECT DISTINCT in MYSQL
SELECT name, type, state, country FROM table GROUP BY name;
should do the trick.
Mysql select query with distinct option
Use
SELECT class, id
FROM table
GROUP BY class
The DISTINCT
option produces all unique combinations of the selected columns, not just the first column. Use GROUP BY
if you need to make a subset of the columns distinct.
Note that this will select an arbitrary id
from all the rows with the same class
. So you might get
class id
8 22
9 11
or
class id
8 22
9 33
MySQL: SELECT UNIQUE VALUE
Try to use DISTINCT like this:
SELECT DISTINCT mycolumn FROM mytable
EDIT:
Try
select mycolumn, count(mycolumn) c from mytable
group by mycolumn having c = 1
Converting SELECT DISTINCT ON queries from Postgresql to MySQL
There's not an exact equivalent to convert a Postgresql query that makes use of SELECT DISTINCT ON to MySQL.
Postgresql SELECT DISTINCT ON
In Postgresql, the following query will eliminate all rows where the expressions (col1, col2, col3)
match, and it will only keep the "first col4, col5 row" for each set of matched rows:
SELECT DISTINCT ON (col1, col2, col3) col4, col5
FROM tablename
So if your table is like this:
col1 | col2 | col3 | col4 | col5
--------------------------------
1 | 2 | 3 | 777 | 888
1 | 2 | 3 | 888 | 999
3 | 3 | 3 | 555 | 555
our query will keep just one row for (1,2,3) and one row for (3,3,3). The resulting rows will then be:
col4 | col5
-----------
777 | 888
555 | 555
please notice that the "first row" of each set is unpredictable, our fist row might be (888, 999) as well unless we specify an ORDER BY:
SELECT DISTINCT ON (col1, col2, col3) col4, col5
FROM tablename
ORDER BY col1, col2, col3, col4
(the DISTINCT on expressions must match the leftmost ORDER BY expressions, but the ORDER BY can contain additional expressions).
MySQL extension to GROUP BY
MySQL extends the use of GROUP BY so that we can select nonaggregated columns not named in the GROUP BY clause. Whenever we select nonaggregated columns the server is free to choose any value from each group from that column, so the resulting values will be indetermined.
So this Postgresql query:
SELECT DISTINCT ON (col1, col2, col3) col4, col5
FROM tablename
can be considered equivalent to this MySQL query:
SELECT col4, col5
FROM tablename
GROUP BY col1, col2, col3
both Postgresql and MySQL will return the "First row" for each (col1, col2, col3), and in both cases the row returned is unpredictable because we didn't specify and order by clause.
A lot of people would be very tempted to convert this Postgresql query with an ORDER BY:
SELECT DISTINCT ON (col1, col2, col3) col4, col5
FROM tablename
ORDER BY col1, col2, col3, col4
with this one:
SELECT col4, col5
FROM (
SELECT col1, col2, col3, col4, col5
FROM tablename
ORDER BY col1, col2, col3, col4
) s
GROUP BY col1, col2, col3
the idea here is to apply an ORDER BY to a subquery so that when MySQL groups by col1, col2, col3 it will keep the first encountered value for col4 and col5. The idea is good, but it's wrong! MySQL is free to choose any value for col4 and col5, and we don't know which are the first values encountered, it depends on the optimizer. So I would correct it to this:
SELECT t1.col4, t1.col5
FROM tablename t1 INNER JOIN (SELECT col1, col2, col3, MIN(col4) as m_col4
FROM tablename
GROUP BY col1, col2, col3) s
ON t1.col1=s.col1
AND t1.col2=s.col2
AND t1.col3=s.col3
AND t1.col4=s.m_col4
GROUP BY
t1.col1, t1.col2, t1.col3, t1.col4
but this is starting to get more complicated.
Conclusion
As a general rule, there's not an exact way to convert a Postgresql query to a MySQL query, but there are a lot of workarounds, the resulting query might be as simple as the original one or it might become very complicated, but it depends on the query itself.
MySQL: Select DISTINCT / UNIQUE, but return all columns?
You're looking for a group by:
select *
from table
group by field1
Which can occasionally be written with a distinct on statement:
select distinct on field1 *
from table
On most platforms, however, neither of the above will work because the behavior on the other columns is unspecified. (The first works in MySQL, if that's what you're using.)
You could fetch the distinct fields and stick to picking a single arbitrary row each time.
On some platforms (e.g. PostgreSQL, Oracle, T-SQL) this can be done directly using window functions:
select *
from (
select *,
row_number() over (partition by field1 order by field2) as row_number
from table
) as rows
where row_number = 1
On others (MySQL, SQLite), you'll need to write subqueries that will make you join the entire table with itself (example), so not recommended.
Mysql query to select Distinct Records on conditions?
count
skips null
values. So you can count
a case
expression where the value is 0
, and then use a having
condition to check that this count is equal to the total count:
SELECT ext_no, MAX(value)
FROM test
GROUP BY ext_no
HAVING COUNT(*) > 2 AND
COUNT(*) = COUNT(CASE value WHEN 0 THEN 1 END)
SQL query to select distinct from data table along with the corresponding column values
You can use DISTINCT
to select
your expection columns.
SELECT DISTINCT DoggoName,DoggoName,DoggoAge
FROM DataTable
How to use SELECT DISTINCT ON with MySQL and Rails
DISTINCT ON
is a Postgres specific extension to the standard SQL DISTINCT
. Neither of them is a "function", both are SQL key words - even though the parentheses required after DISTINCT ON
make it look like a function.
There are a couple of techniques to rewrite this with standard-SQL, all of them more verbose, though. Since MySQL does not support window-functions row_number()
is out.
Details and more possible query techniques:
Select first row in each GROUP BY group?
Fetch the row which has the Max value for a column
Rewritten with NOT EXISTS
:
SELECT *
FROM delivery_service_prices d1
WHERE active = 1
AND 2808.0 BETWEEN min_weight AND max_weight
AND 104.0 BETWEEN min_length AND max_length
AND 104.0 BETWEEN min_thickness AND max_thickness
AND NOT EXISTS (
SELECT 1
FROM delivery_service_prices d2
WHERE active = 1
AND 2808.0 BETWEEN min_weight AND max_weight
AND 104.0 BETWEEN min_length AND max_length
AND 104.0 BETWEEN min_thickness AND max_thickness
AND d2.delivery_service_id = d1.delivery_service_id
AND d2.price < d1.price
AND d2.<some_unique_id> < d1.<some_unique_id> -- tiebreaker!
)
ORDER BY delivery_service_id
If there can be multiple rows with the same price for the same
delivery_service_id
, you need to add some unique tie-breaker to avoid multiple results perdelivery_service_id
. At least if you want a perfectly equivalent query. My example would select the row with the smallest<some_unique_id>
from each set of dupes.Unlike with
DISTINCT ON
,ORDER BY
is optional here.
Related Topics
Oracle Unique Constraint Based on Column Value
Have Pl/Sql Outputs in Real Time
"Pivoting" a Table in SQL (I.E. Cross Tabulation/Crosstabulation)
How to Find Tables Which Reference a Particular Row via a Foreign Key
Extract Email Address from String Using Tsql
Execute Procedure in a Trigger
Creating Trigger That Runs on Two Tables
What Is The Purpose (Or Use Case) for an Outer Join in Sql
Order by Column1 If Column1 Is Not Null, Otherwise Order by Column2
Sql - Select Max() and Accompanying Field
Kill All User Connections in SQL Azure
Dividing 2 Numbers in SQL Server
How to Delete Duplicate Records in Sql
Writing a Recursive SQL Query on a Self-Referencing Table
How to Create Trigger to Keep Track of Last Changed Data
How to Use Group_Concat in Rails