Fetch nth row after mth row in mysql

Question

I want to fetch some particular row after I fetch all rows from table 'user' . 'user' table has 10 records like this

1 . ABCD
2 . hfdff
3 . uiyuiyoy
4 . uytitito
5 . piooioi
6 . GXX jhjkhh
7 . GXX uyyto
8 . GXX upupu
9 . tytuyur
10 . zvsfsgsg

So I want to show results like this

1 . ABCD
2 . hfdff
3 . uiyuiyoy
4 . uytitito
5 . piooioi
6 . tytuyur
7 . zvsfsgsg 
8 . GXX jhjkhh
9 . GXX uyyto
10 . GXX upupu

I want to fetch records that starts with GXX at the last . Can it be done? Can anyone tell me how to achieve this ? what will be the query for this? Please help .

before GXX ABCD . I meant to say I want to fetch all GXX records at last — Maximious, Sep 19 '19 at 10:05
Adjust the sample data, it could need some more difficulties. (Like 'ABCD10', and 'XYZ ABCD1'.) — jarlh, Sep 19 '19 at 10:07

Barbaros Özhan · Accepted Answer · 2019-09-19T11:22:28.847

1

You can use case..when expression with substr() function :

order by case when substring(str,1,3) = 'GXX' then 1 else 0 end, str

this puts values starting with GXX at the bottom, but if you exactly order according to your desired output, prefer using this, rather :

order by case when substring(str,1,3) = 'GXX' then 1 else 0 end, case when substring(str,5,1) = 'u' then str else 'z' end desc

Demo

edited Sep 19 '19 at 11:22

answered Sep 19 '19 at 10:33

Barbaros Özhan

59,113
10
31
55

it worked for me but I am using SQL server so I changed substr to substring – Maximious Sep 19 '19 at 11:05
well I converted from MySQL to SQL Server @Maximious – Barbaros Özhan Sep 19 '19 at 11:24

score 0 · Answer 2 · answered Sep 19 '19 at 10:04

0

Add ORDER BY columnname ASC at the end of your SELECT

answered Sep 19 '19 at 10:04

Megu bhai

61
1
3

I did this but problem is some user has their name starting with T . then results like this ABCD , ABCD1 , ABCD3 , ABCD4 , ABD5 , GXX ABCD , GXX ABCD1 , GXX ABCD2 , Tgdjggjdg like this . I want to fetch records that are starting with GXX at last – Maximious Sep 19 '19 at 10:10

score 0 · Answer 3 · answered Sep 19 '19 at 11:00

0

MySQL treats boolean expressions as numbers, with 0 for false and 1 for true. This is handy in this situation:

order by (col like 'GXX%'),  -- put these last
         col

answered Sep 19 '19 at 11:00

Gordon Linoff

1,242,037
58
646
786

Fetch nth row after mth row in mysql

3 Answers3