Reputation: 432
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
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
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
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