SQL query interleaving two different statuses

Question

I have a table testtable having fields

Id    Name       Status
1     John       active
2     adam       active
3     cristy    incative
4     benjamin  inactive
5     mathew    active
6     thomas    inactive
7     james     active

I want a query that should dispaly the reuslt like

Id    Name       Status

1     John       active
3     cristy    incative
2     adam       active
4     benjamin  inactive
5     mathew    active
6     thomas    inactive
7     james     active

my question is how to take records in the order of active status then inactive then active then inactive etc.. like that from this table.

Why id 2 was adam and suddenly became cristy? What if there is 10 active users and 2 inactive? — Elhana, Nov 05 '14 at 07:10
@Elhana,@Renier,@Madhivanan my question is how to take records in the order of active status then inactive then active then inactive etc.. like that from this table.. — John Mathew, Nov 05 '14 at 07:16
It looks like homework question, nevertheless a hint: 2 queries manipulating rownum to return even and odd numbers in union, then wrap it with select order by rownum — Elhana, Nov 05 '14 at 07:22
@Elhana if there is 10 active users and 2 inactive,then the list should be active,inactive,acitve,inactive,active,active,active,active etc.. — John Mathew, Nov 05 '14 at 07:23

CJBS · Accepted Answer · 2014-11-05T20:13:44.553

This query sorts on interleaved active/inactive state:

SELECT [id], 
       [name], 
       [status] 
FROM   (
           (
             SELECT
                Row_number() OVER(ORDER BY id) AS RowNo, 
                0 AS sorter, 
                [id], 
                [name], 
                [status] 
             FROM   testtable 
             WHERE  [status] = 'active'
           ) 
           UNION ALL 
           (
             SELECT
                Row_number() OVER(ORDER BY id) AS RowNo, 
                1 AS sorter, 
                [id], 
                [name], 
                [status] 
             FROM   testtable 
             WHERE  [status] = 'inactive'
           )
       ) innerUnion 
ORDER  BY ( RowNo * 2 + sorter )

This approach uses an inner UNION on two SELECT statements, one which returns active rows, the other inactive rows. They both have a RowNumber generated, which is later multiplied by two to ensure it's always even. There's a sorter column that's just a bit field, and to ensure that a unique number is available for sorting: adding it to the RowNumber yields either an odd or even number depending on active/inactive state, hence allowing the results to be interleaved.

The SQL Fiddle link is here, to allow testing and manipulation: http://sqlfiddle.com/#!3/8a8a1/11/0

In the absence of a specified DB system, I've assumed that SQL Server 2008 (or newer) is being used. An alternate row numbering system would be necessary on other DBMSes.

score 2 · Answer 2 · answered Nov 05 '14 at 07:53

2

Finally i got the answer

SET @rank=0;
SET @rank1=0;
SELECT @rank:=@rank+1 AS rank,id,name,status FROM `testtablejohn` where status='E'
UNION
SELECT @rank1:=@rank1+1 AS rank,id,name,status FROM `testtablejohn` where status='D'
order by rank

answered Nov 05 '14 at 07:53

John Mathew

395
1
3
16

1

Now you only need to implement the even/odd numbering scheme and you are there. Just start `@rank` at -1 and add +2 to `@rank` and `@rank1` on every row. – wolfgangwalther Nov 05 '14 at 07:57

score 0 · Answer 3 · edited May 23 '17 at 11:57

Since you didn't post any example of what you tried so far, I will limit my answer to the general approach as well.

One approach could be to generate a row number for active rows and a row number for inactive rows. Start your numbering for active at 1 and use only odd numbers (that means increase your counter by 2 every time) and do the same thing with 2 and even numbers for the inactive rows. Put those two counters in the same column.

You will end up with a single column to easily sort on in your ORDER BY clause.

Here are some links that might be useful for you:
MySQL - Get row number on select
http://www.mysqltutorial.org/mysql-case-statement/

Just give it a go with those. If you can't make it work, then show us what you tried so far. Post some example code in the question and we might be able to guide you!

SQL query interleaving two different statuses

3 Answers3