CODEWITHSUNDEEP
phpmysqldatabase
sandip
sandip

Reputation: 3289

How do I sort the following table & get those top5- 5 recoreds,top20- twenty records?

This query giving strange result:

    SELECT `user_id`,`rankType`
    FROM `ranks`
    WHERE `user_id` =23
    AND (`rankType` = "top5"
    OR `rankType` = "top20")
    ORDER BY rankType
    LIMIT 0 , 30

here the SQLfiddle.

Want I am trying to achieve is:

1)To get only 5 records of top5 rank type, 20 records of rank type top20

2)I want to show the result in ascending order of rank type.(but if you see in the demo fiddle it's showing apposite, may be it is only considering 2 from 20 & 5)

Upvotes: 0

Views: 68

Answers (4)

KevInSol
KevInSol

Reputation: 2644

SELECT `id`,`user_id`,`rankType`
FROM `ranks`
WHERE `user_id` =23
AND `rankType` = "top5" limit 5
union
SELECT `id`,`user_id`,`rankType`
FROM `ranks`
WHERE `user_id` =23
AND `rankType` = "top20" limit 20

Upvotes: 1

C3roe
C3roe

Reputation: 96455

One possibility without doing two queries and UNIONing them:

ORDER BY FIND_IN_SET(rankType,'top5,top20')

Upvotes: 0

J0HN
J0HN

Reputation: 26961

(SELECT `id`,`user_id`,`rankType`
FROM `ranks`
WHERE `user_id` =23
AND `rankType` = "top5"
ORDER BY rankType
LIMIT 0, 5)

union 

(SELECT `id`,`user_id`,`rankType`
FROM `ranks`
WHERE `user_id` =23
AND `rankType` = "top20"
ORDER BY rankType
LIMIT 0, 20)

If later on you want to add another set of sorting/filtering columns, wrap it all into something like

select * from ( /* previous query goes here */ ) tt
where id > 100
order by id

Note that ranktype is varchar, so it's sorted lexicographically, so top20 < top5. You'll have to employ natural sorting or some other means to get it right.

Upvotes: 3

harsh
harsh

Reputation: 7692

Your result is actually in ascending order, since column rank_type is of varchar type top20 comes first than top5 as in string comparison.

If you only want to deal between top5 and top20, a dirty solution could be:

ORDER BY rankType desc

Upvotes: 0

Related Questions