CODEWITHSUNDEEP
mysqlsql
Ankit Bajpai
Ankit Bajpai

Reputation: 13509

Order by a specific condition

I have a table in MySQL - 

 ABC (User, Login_time, City)

and contains some data in it.

The result I want is Top 5 users form Noida should come at the top in the resultset and rest resultset should be sorted by login_time. See the fiddle here -

http://www.sqlfiddle.com/#!9/88c203/9

create table abc (user varchar(20), login_time date, city varchar(20));

insert into abc values ('A', curdate(), 'Noida'),
                   ('B', '2017-04-16', 'Delhi'),
                   ('C', '2017-04-14', 'Noida'),
                   ('D', '2017-04-18', 'Kanpur'),
                   ('E', '2017-04-13', 'Raebareli'),
                   ('F', '2017-04-16', 'Sitapur'),
                   ('G', '2017-04-17', 'Muradabad'),
                   ('H', '2017-04-18', 'Noida'),
                   ('I', '2017-04-13', 'Lucknow'),
                   ('J', '2017-04-12', 'Noida');

The expected result is - 

'A', 2017-04-19, 'Noida'
'C', '2017-04-14', 'Noida'
'H', '2017-04-18', 'Noida'
'D', '2017-04-18', 'Kanpur'
'G', '2017-04-17', 'Muradabad'
'B', '2017-04-16', 'Delhi'
'F', '2017-04-16', 'Sitapur'
'E', '2017-04-13', 'Raebareli'
'I', '2017-04-13', 'Lucknow'
'J', '2017-04-12', 'Noida'

And the query i tried is - 

select * from (select user, login_time, city from abc
where city = 'Noida'
limit 3
union all 
select user, login_time, city from abc
where user not in (select user, login_time, city from abc
where city = 'Noida'
limit 3)
limit 4, count)
order by login_time

When i try to execute the query in fiddle it throws error - 

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery

Any help would be greatly appreciated.

Upvotes: 1

Views: 80

Answers (4)

KindaTechy
KindaTechy

Reputation: 1173

Try something like this:

select *, 1 as OrderHelp
    from abc
    where city = 'Noida'
    limit 3
union all
select *, 2 as OrderHelp
    from abc
    where user not in (
        select user
            from abc
            where city = 'Noida'
            limit 3
    )
    order by OrderHelp, login_time desc

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

You can use parentheses to combine the separately ordered results.

(select user, login_time, city 
 from abc
 where city = 'Noida'
 --order by login_time desc > add this to sort by login_time 
 limit 5
)
union all
(select user, login_time, city 
 from abc
 where city <> 'Noida'
 order by login_time desc
 limit 3
)

Because you have to include the non-top 5 rows from Noida in the result-set, you can use a left join to get those and order by the login_time.

(select user, login_time, city 
 from abc
 where city = 'Noida'
 order by login_time desc
 limit 3
)
union all
(select a1.user, a1.login_time, a1.city 
 from abc a1
 left join (select user, login_time, city 
            from abc
            where city = 'Noida'
            order by login_time desc
            limit 3) a2 --this derived table is the previous result-set of top n users from Noida
 on a1.user=a2.user and a1.city= a2.city
 where a2.user is null       
 order by a1.login_time desc
 limit 7
 )

Use a prepared statement to get the number of remaining rows other than the top 3 rows.

set @a= (select count(*) - 3 from abc);

prepare stmt from
'(select user, login_time, city 
  from abc
  where city = ''Noida''
  order by login_time desc
  limit 3)
 union all
(select a1.user, a1.login_time, a1.city 
 from abc a1
 left join (select user, login_time, city 
            from abc
            where city = ''Noida''
            order by login_time desc
            limit 3) a2
 on a1.user=a2.user and a1.city= a2.city
 where a2.user is null       
 order by a1.login_time desc
 limit ?
 )';

execute stmt using @a;

Upvotes: 1

Peter
Peter

Reputation: 16923

this will work if you are sure you'll have 3 record with city='noida'

select * 
from
(
    (
      select user,login_timecity
      from abc
      where city='noida'
      order by login_time
      limit 3
    )
    union
    (
      select user,login_timecity
      from abc
      where x
      order by city='noida' DESC, login_time
      limit 3, x
    )
) t

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269513

If you want the users from Noida first, just use:

select abc.*
from abc
order by (city = 'Noida') desc,
         login_time desc;

Upvotes: 0

Related Questions