CODEWITHSUNDEEP
mysqlsqlgroup-bysql-order-by
Wasim A.
Wasim A.

Reputation: 9890

MySQL order by lowest and group by

Table Data: select firstName, package_id, website from user

Please note, there are more than 30k rows in table.

enter image description here

I am trying to display lowest package_id in each website and here is query

select firstName, package_id, website from user group by website order by package_id

enter image description here

It should display lowest package_id from each website but its incorrect, like for value kahuta it should return 6 and for Null => 10 and example.com => 9

Upvotes: 0

Views: 60

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269633

Don't use aggregation. Use filtering:

select u.*
from user u
where u.package_id = (select min(u2.package_id)
                      from user u2
                      where u2.website <=> u.website
                     );

If performance is an issue, you want an index on user(website). However, you should check if it is used for <=>. If not, then try using = and then additional logic for the NULL values.

Upvotes: 1

forpas
forpas

Reputation: 164089

With NOT EXISTS:

select u.* from user u
where not exists (
  select 1 from user
  where website <=> u.website and package_id < u.package_id
)

or join the table to a subquery that returns the min package_id for each website:

select u.* 
from user u inner join (
  select website, min(package_id) minpackageid
  from user 
  group by website
) g on g.website <=> u.website and g.minpackageid = u.package_id

Upvotes: 1

Related Questions