MySQL - show field value only in first instance of each grouped value?

Question

I don't think this is possible, but I would like to be proved otherwise.

I have written a simple report viewing class to output the results of various database queries. For the purpose of improving the display, when I have a report with grouped data, I would like to display the field value only on the first row of each unique value - and I would like to do this at the query level, or it would necessitate additional logic in my class to determine these special values.

It will probably help to illustrate my requirements with a simple example. Imagine this dataset:

Year    Quarter    Total
2008    Q1         20
2008    Q2         25
2008    Q3         35
2008    Q4         40
2009    Q1         15
2009    Q2         20
2009    Q3         30
2009    Q4         35

If possible, I would like the dataset returned as:

Year    Quarter    Total
2008    Q1         20
        Q2         25
        Q3         35
        Q4         40
2009    Q1         15
        Q2         20
        Q3         30
        Q4         35

Is there any way of doing this progammatically in MySQL?

Answer 1

SELECT  CASE WHEN @r = year THEN NULL ELSE year END AS year,
        quarter,
        total,
        @r := year
FROM    (
        SELECT  @r := 0
        ) vars,
        mytable
ORDER BY
        year

@r here is a session variable. You can use these in MySQL like any variable in any procedural language.

First, it's initialized to zero inside the subquery.

Second, it's checked in the SELECT clause. If the current value of @r is not equal to year, then the year is output, else NULL is output.

Third, it's updated with current value of year.

Answer 2

Why would you want to do this? What about existing records where the Year column is empty or null?

Beautifying the output belongs inside the report logic. In pseudocode it would be sth. like:

var lastYear = 0
foreach (record in records)
{
   if (record.Year == lastYear)
   {
     print "   "
   }
   else
   {
     print record.Year
     lastYear = record.Year
   }

   // print the other columns
 }

Answer 3

Not the answer you asked for, but...

Sounds like an iffy thing to be doing in MySQL in the first place. Just looking at the raw rows of data, 2008 and 2009's Q2s don't seem to make much sense as data rows. The issue is presentational, not a matter of fetching data. Sounds more like something to be written into your viewing class - when passed a certain parameter, for example, it will know not to repeat things like "2008".

This allows for greater reusability of code, as well: rather than rewriting the query when you want to present the data differently, say by quarters rather than be year, you can just change one of the arguments of the viewing class so that the same query with a different order clause can output:

Quarter   Year   Total
Q1        2008   20
          2009   15
Q2        2008   25
          2009   20
...

Answer 4

It does not exactely match your request but I would rather pivot my table. It allows to visually compare figures from the 2 years as you have one quarter per column:

SELECT Year,
SUM(IF(Quarter="Q1", Rev, 0)) AS Q1,
SUM(IF(Quarter="Q2", Rev, 0)) AS Q2,
SUM(IF(Quarter="Q3", Rev, 0)) AS Q3,
SUM(IF(Quarter="Q4", Rev, 0)) AS Q4
FROM t1 GROUP BY 1
ORDER BY 1

You then have:

YEAR Q1 Q2 Q3 Q4
2008
2009