-1

I have a table users:

users

Where id is a primary key.

I want to select all columns, but all usernames should be unique. I don't care which ids will be in an expected result, but anyway I need them. For that I use the following query in Posgres 10:

select distinct on (username) * from users;

That gives me the result I want:

enter image description here

How can I achieve the same, but using MySQL query?

Duelist
  • 1,562
  • 1
  • 9
  • 24
  • Maybe this was asked before, but I didn't find the similar problem. It would be very helpful if you could share a link if my question is a duplicate instead of simply downwoting – Duelist Jan 24 '19 at 18:45
  • Possible duplicate of [Converting SELECT DISTINCT ON queries from Postgresql to MySQL](https://stackoverflow.com/questions/17673457/converting-select-distinct-on-queries-from-postgresql-to-mysql) – jmoerdyk Jan 24 '19 at 19:00

2 Answers2

1

Your query doesn't make sense in Postgres because it lacks an order by. For this query:

select distinct on (username) u.*
from users u
order by username, id desc;

You can write this as:

select u.*
from users u
where u.id = (select max(u2.id) from users u2 where u2.username = u.username);

Assuming id is unique, this will return one row per username.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I believe this is the conversion of that SELECT id, username FROM users group by id

You can find more info on this link:Converting SELECT DISTINCT ON queries from Postgresql to MySQL

Extra Note: You can use this http://www.sqlfiddle.com/#!9/0bd1a2/1 to test your SQL which maybe helpful for you in converting Postgres to SQL

Xion
  • 374
  • 2
  • 7
  • This query returns result with only **username** column, but I need **id** as well – Duelist Jan 24 '19 at 18:54
  • ERROR: column "users.id" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT id, username FROM users group by username; – Duelist Jan 24 '19 at 18:59
  • Actually **id** is primary key – Duelist Jan 24 '19 at 19:00
  • I want to say use this `SELECT id, username FROM users; WHERE (SELECT DISTINCT(id) From users)` however this code may give you some other errors as I do not how your database is set up from your case. I still think a group by would be fitting to use. – Xion Jan 24 '19 at 19:20