CODEWITHSUNDEEP
mysqlsql
eric_the_animal
eric_the_animal

Reputation: 432

mySQL select distinct and add unique value

I'd like to pick the brains of any sql expert who can tell me how I can select the distinct values from a field and then add a unique ID to each set of distinct values. I can write a quick bit of code to do this but I need it in a query. Important to add that I need the unique value to start at 1 (otherwise yes I know I can use the existing ID). So it will look like this:

Patient_ID   New_Unique_Value
23            1
23            1
23            1
4378          2
4378          2
48            3
48            3
48            3
48            3

I can write the Patient_IDs to a temp table but I can't find any info on dynamically adding a unique increment.

Upvotes: 0

Views: 831

Answers (3)

eric_the_animal
eric_the_animal

Reputation: 432

My solution using a temporary table

CREATE TABLE IF NOT EXISTS OAK_origres.TEMP2 (PATID INTEGER, NEWID INTEGER AUTO_INCREMENT, PRIMARY KEY (NEWID));

INSERT INTO OAK_origres.TEMP2 (PATID) 
SELECT DISTINCT OAK_origres.`Original Results`.`Patient ID` FROM OAK_origres.`Original Results` 
INNER JOIN OAK_patient.Demographic ON OAK_origres.`Original Results`.`Patient ID` = OAK_patient.Demographic.`System ID` 
WHERE `Import Date` = '2014-09-23 13:00:00';

What I needed was smaller values that I could use to differentiate the values of 'Import Date'. I used the autoincrement values for this:

UPDATE OAK_origres.`Original Results` 
INNER JOIN OAK_origres.TEMP2 
    ON OAK_origres.`Original Results`.`Patient ID` = OAK_origres.TEMP2.PATID 
SET `Import Date`=DATE_ADD(`Import Date`, INTERVAL  OAK_origres.TEMP2.NEWID SECOND)

Upvotes: 0

Juan Figueredo
Juan Figueredo

Reputation: 26

you can use something like this

set @a:=0;
SELECT alpha2.ndx, beta.item
FROM (
   SELECT alpha.item, (@a := @a + 1) as ndx from (
       SELECT DISTINCT item FROM tbl
   ) alpha
) AS alpha2 INNER JOIN tbl AS beta ON beta.item = alpha2.item

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271151

The simplest way in MySQL is to use variables:

select p.Patient_ID, 
       (@rn := if(@p = p.Patient_ID, @rn,
                  if(@p := p.Patient_ID, @rn + 1, @rn + 1)
                 )
       ) as New_Unique_Value
from t cross join
     (select @rn := 0, @p := -1) params
order by patient_id;

Upvotes: 1

Related Questions