Mark last row as a column in MySQL

Question

How would I go about doing SELECT on a MySQL table where i would create a column with value 0 for all rows except for the last row that would be marked as 1. is it even possible?

Example:

Real table:

    +----+----------------------+
    | id | schedule_name        |
    +----+----------------------+
    |  1 | Global Fee Schedule  |
    |  2 | CAT Fee Schedule     |
    |  3 | Daily Fee Schedule   |
    |  4 | Daily Claim Schedule |
    |  5 | Wind/Hail Schedule   |
    |  6 | Daily Claim Schedule |
    |  7 | Daily Claims         |
    |  8 | CAT Fee Schedule     |
    |  9 | Daily Claims         |
    | 10 | Daily Claims         |
    +----+----------------------+

with added column:

    +----+----------------------+-------------+
    | id | schedule_name        | last_column |
    +----+----------------------+-------------+
    |  1 | Global Fee Schedule  |           0 |
    |  2 | CAT Fee Schedule     |           0 |
    |  3 | Daily Fee Schedule   |           0 |
    |  4 | Daily Claim Schedule |           0 |
    |  5 | Wind/Hail Schedule   |           0 |
    |  6 | Daily Claim Schedule |           0 |
    |  7 | Daily Claims         |           0 |
    |  8 | CAT Fee Scedule      |           0 |
    |  9 | Daily Claims         |           0 |
    | 10 | Daily Claims         |           1 |
    +----+----------------------+-------------+

Answer 1

Not sure why you should need something like this. But yes this is possible but not very easy to do.

Create/insert data

CREATE TABLE iascat_fee_categories
    (`id` INT, `schedule_name` VARCHAR(20))
;

INSERT INTO iascat_fee_categories
    (`id`, `schedule_name`)
VALUES
    (1, 'Global Fee Schedule'),
    (2, 'CAT Fee Schedule'),
    (3, 'Daily Fee Schedule'),
    (4, 'Daily Claim Schedule'),
    (5, 'Wind/Hail Schedule'),
    (6, 'Daily Claim Schedule'),
    (7, 'Daily Claims'),
    (8, 'CAT Fee Schedule'),
    (9, 'Daily Claims'),
    (10, 'Daily Claims')
;

The select query that marks the last_column as 1 is pretty complex.

Select LIMIT 0, 10

SELECT 
   iascat_fee_categories.id
 , iascat_fee_categories.schedule_name
 , IF((max_id_counts.max_sum_id_count + 0)= iascat_fee_categories.id, 1, 0) last_column
FROM (
  SELECT 
   SUM(id_counts.id_count) max_sum_id_count
  FROM (
     SELECT
       COUNT(*) id_count
     FROM 
      iascat_fee_categories
     GROUP BY
      iascat_fee_categories.id 
     LIMIT 0, 10
  ) AS id_counts
) AS max_id_counts
CROSS JOIN 
  iascat_fee_categories
LIMIT 0, 10

Output

    id  schedule_name         last_column  
------  --------------------  -------------
     1  Global Fee Schedule               0
     2  CAT Fee Schedule                  0
     3  Daily Fee Schedule                0
     4  Daily Claim Schedule              0
     5  Wind/Hail Schedule                0
     6  Daily Claim Schedule              0
     7  Daily Claims                      0
     8  CAT Fee Schedule                  0
     9  Daily Claims                      0
    10  Daily Claims                      1

But if you want to SELECT LIMIT 1, 10 the select query changes. please note you also need to update this part IF((max_id_counts.max_sum_id_count + 0) to IF((max_id_counts.max_sum_id_count + 1) And ofcource both LIMIT statements

and with select LIMIT 2, 10 you need the change that to IF((max_id_counts.max_sum_id_count + 2) And ofcource both LIMIT statements

SELECT LIMIT 1, 10

SELECT 
   Table1.id
 , Table1.schedule_name
 , IF((max_id_counts.max_sum_id_count + 1)= iascat_fee_categories.id, 1, 0) last_column
FROM (
  SELECT 
   SUM(id_counts.id_count) max_sum_id_count
  FROM (
     SELECT
       COUNT(*) id_count
     FROM 
      iascat_fee_categories
     GROUP BY
      Table1.id 
     LIMIT 1, 10
  ) AS id_counts
) AS max_id_counts
CROSS JOIN 
  iascat_fee_categories
LIMIT 1, 10

Output

    id  schedule_name         last_column  
------  --------------------  -------------
     2  CAT Fee Schedule                  0
     3  Daily Fee Schedule                0
     4  Daily Claim Schedule              0
     5  Wind/Hail Schedule                0
     6  Daily Claim Schedule              0
     7  Daily Claims                      0
     8  CAT Fee Schedule                  0
     9  Daily Claims                      0
    10  Daily Claims                      1

Answer 2

You can use max function. For example:

select *, id=(select max(id) from YOUR_TAB) as last_column from YOUR_TAB