MySQL - How to Order Results by Alternating (1,2,3, 1, 2, 3, 1, 2, 3,) Rows, Is It Possible

Mysql - How do I order results by alternating (1,2,3, 1, 2, 3, 1, 2, 3,) rows, is it possible?

Use:

SELECT x.client_id, 
       x.project_id,
       x.project_name
  FROM (SELECT t.client_id,
               t.project_id,
               t.project_name,
               CASE
                 WHEN @client_id != t.client_id THEN @rownum := 0
                 WHEN @client_id = t.client_id THEN @rownum := @rownum + 1
                 ELSE @rownum 
               END AS rank,
               @client_id := t.client_id
          FROM TABLE t,
               (SELECT @rownum := 0, @client_id
      ORDER BY t.client_id) r) x
ORDER BY x.rank, x.client_id

MySQL doesn't have any ranking functionality, but luckily you can use variables. The key was resetting the @rownum value when the client_id doesn't match the previous client_id - the ORDER BY in the subquery is to ensure that clients are in order.

Fix query that orders MYSQL results by alternating 1,2,3 - 1,2,3 - 1,2,3

you are missing a ,

t.applet , CASE WHEN @site ...
         ^................................here

add the where here:

FROM ".$table." t,
(SELECT @rownum := 0, @site ORDER BY t.site) r) x 

WHERE bio LIKE 'value'

ORDER BY x.rank, x.site

Php or MYSQL solution on how to display MYSQL data by alternating rows depending on a criteria

So, you have a category (Make) and a vehicle (Vehicle ID), and you want every page to show one Vehicle from every Make, unless there are no more vehicles left on that make?

Assuming you have a table Vehicles with the fields make and vin:

first find the makes:

$q = "SELECT make FROM Vehicles"
$r=mysqli_query($dbconnect, $q);
$make= mysqli_fetch_array($r, MYSQLI_ASSOC);
array_unshift($make, "nomake"); //add so the first make is not a position zero

Then, build an array that matches the vin to the make:

$q = "SELECT make, vin FROM Vehicles ORDER BY make"
$r=mysqli_query($dbconnect, $q);
while($record = mysqli_fetch_array($r, MYSQLI_ASSOC)){
  $vehicle[$record['vin']]=$record['make'];
}

Then, build your array in the order for your page:

$onpage=0;
$numperpage = 5; //number of vehicles per page
foreach($vehicle as $vkey => $vvalue){
  if (array_search($vvalue,$lastvalue)){
    $finalorder[$vkey] = $vvalue;
    $lastvalue[$onpage]= $vvalue;
    $onpage=$onpage<$numperpage?$onpage++:0;
  }
  if($onpage==0){
    unset($lastvalue);
  }
}
var_dump($finalorder);

This may need a little cleaning up, but it should work.

MySQL Order By alternate values

Here's one option using user-defined variables. Basically it creates a Row Number per Group, and then orders by it along with the rec_f_id field:

SELECT rec_id, rec_f_id
FROM (
  SELECT rec_id, rec_f_id,
    @rn:=IF(@prev=rec_f_id,@rn+1,1) rn,
    @prev:=rec_f_id
  FROM tbl_records
    JOIN (SELECT @rn:=0, @prev:=0) t
  ORDER BY rec_f_id
  ) t
ORDER BY rn, rec_f_id

• Sample SQL Fiddle

MySQL: Is it possible to return a mixed dataset?

Something like this query should do:

Select some_value, x, c
From
(
  Select
    some_value, x,
    Case When x=1 Then @c1 Else @c2 End As c,
    @c1 := Case When x=1 Then @c1+2 Else @c1 End As c1,
    @c2 := Case When x=2 Then @c2+2 Else @c2 End As c2
  From test_data, (Select @c1:=0, @c2:=1) v
  Order By some_value
) sub
Order By c

It assigns unique even numbers to x=0, and odd numbers to x=1, and uses these values as sort criteria.

---

It returns

some_value  x  c
A           1  0
X           2  1
B           1  2
Y           2  3
C           1  4
Z           2  5

for the following test-data:

Create Table test_data (
  some_value VARCHAR(10),
  x  INT
);

Insert Into test_data Values('A', 1);
Insert Into test_data Values('B', 1);
Insert Into test_data Values('C', 1);
Insert Into test_data Values('Z', 2);
Insert Into test_data Values('Y', 2);
Insert Into test_data Values('X', 2);

---

Within the alternating rule values are sorted by some_value, you can change this in the inner select, or add your conditions there.

If there are more values of a certain type (1 or 2), you get them after the rest (1 2 1 2 2 2).

How to count in 2 different ways in the same select?

This query will give you what you need and it works on mysql 5.x

You have to REPLACE the 11 and other Value that are same with Replace, or else this wouldn't work.

The t1 table that is left joined has actually all the sample_ids that are doubles, so if you b´need that as well, you can get it from that query

SELECT 
    t.`customer_id`
    , COUNT(DISTINCT (t.`sample_id`)) total_sample
    , MAX(IF(t1.count_ IS NULL,0,1)) ns_sample
FROM
    (SELECT 
        `customer_id`, REPLACE(`sample_id`, '11', '1') `sample_id`
    FROM
        mytable) t
        LEFT JOIN
    (SELECT 
        `customer_id`, `sample_id`, COUNT(*) count_
    FROM
        (SELECT 
        `customer_id`, REPLACE(`sample_id`, '11', '1') `sample_id`
    FROM
        mytable) mytable
    GROUP BY `customer_id` , `sample_id`
    HAVING count_ > 1) t1 ON t.`customer_id` = t1.`customer_id`
GROUP BY `customer_id`;

Schema (MySQL v5.7)

CREATE TABLE mytable (
  `customer_id` INTEGER,
  `sample_id` INTEGER
);

INSERT INTO mytable
  (`customer_id`, `sample_id`)
VALUES
  ('1', '1'),
  ('1', '11'),
  ('1', '2'),
  ('2', '3'),
  ('2', '4'),
  ('2', '5'),
  ('2', '5');

---

Query #1

SELECT 
    t.`customer_id`
    , COUNT(DISTINCT (t.`sample_id`)) total_sample
    , MAX(IF(t1.count_ IS NULL,0,1)) ns_sample
FROM
    (SELECT 
        `customer_id`, REPLACE(`sample_id`, '11', '1') `sample_id`
    FROM
        mytable) t
        LEFT JOIN
    (SELECT 
        `customer_id`, `sample_id`, COUNT(*) count_
    FROM
        (SELECT 
        `customer_id`, REPLACE(`sample_id`, '11', '1') `sample_id`
    FROM
        mytable) mytable
    GROUP BY `customer_id` , `sample_id`
    HAVING count_ > 1) t1 ON t.`customer_id` = t1.`customer_id`
GROUP BY `customer_id`;

| customer_id | total_sample | ns_sample |
| ----------- | ------------ | --------- |
| 1           | 2            | 1         |
| 2           | 3            | 1         |

---

View on DB Fiddle

---

Related Topics