Return value 1 on FIRST INSTANCE of unique

Question

I have a table with Name and Area. I want to run a query that returns that table with the addition of a 'Special_COUNT' The count returns 1 on the firstmost Concat(Distinct(Name,Area); should return 0 otherwise For example:

Name          |          Area          |     Special_COUNT      |      

ABCD          |          US            |          1             |
ABCD          |          US            |          0             |*Same Name/Region no value
ABCD          |          Asia          |          1             |*New Region
ABCDX         |          Asia          |          1             |*New Name

How can I get the above 'Special_COUNT' column into the results of my query?

Answer 1

MySQL doesn't have any ranking functionality - you'll want to read this article for setting up the variables for LEAD functionality in order to number the output correctly.

Use:

SELECT x.name,
       x.area,
       x.special_count
  FROM (SELECT t.name,
               t.area,
               CASE WHEN @name = t.name AND @area = t.area THEN 0 ELSE 1 END AS special_count,
               @name := t.name, 
               @area := t.area
          FROM TABLE t, 
               (SELECT @area := NULL, @name := NULL) r) x

Tested on 4.1 - results match, thanks for supplying test data.

Answer 2

Thanks!

Your code looks so simple, alas I am not able to work this into a larger query (apologies for not providing an example query so that you could show me how this would work, below I have done that for you in the hopes that you can explain by example)

So given this original query below how would I revise it to incorporate your data with the goal of obtaining a 'special_count' on allanimalnames.AnimalName per location.Area

I see the light at the end of the tunnel...

SELECT
CASE allanimalnames.animalID
    WHEN allanimalnames.animalID = 1 THEN "Dangerous!"
    WHEN allanimalnames.animalID <> 1 THEN "Cuddly"
END AS 'Animal Danger Levell,

allanimalsizes.SizeInFeet AS 'Length of the Animal',
allanimaltypes.AnimalTypeName AS 'Carnivor or Herbavore?',
location.Area AS 'Region this Animal is found in',
allanimalnames.AnimalName AS 'Name of this animal,
MAX(report.reportdate) AS 'Last Reported'
FROM
allanimalnames
INNER JOIN allanimaltypes ON allanimaltypes.atypeid = allanimalnames.atypeid
INNER JOIN allanimalsizes ON allanimalsizes.sizeid = allanimalnames.sizeid
INNER JOIN location ON location.locid = allanimalnames.locid
INNER JOIN report ON report.reportid = allanimalnames.reportid
WHERE
report.reportdate
BETWEEN '2008-01-01' AND '2009-01-01'
GROUP BY
allanimalsizes.SizeInFeet
allanimaltypes.AnimalTypeName
location.Area
allanimalnames.AnimalName
report.reportdate