CODEWITHSUNDEEP
mysqlsqlview
Geoff_S
Geoff_S

Reputation: 5107

Selecting row count to use as arbitrary row ID

I'm trying to alter a current mysql view I have so that when I export the view I have a unique row ID.

I'm trying to find a way to possibly use the row number or an auto increment function but I don't know how to do this in a view.

Here is the query:

alter view daily_report as SELECT 
@id := @id + 1 as id
,c.extension as Extension
,RESPONSIBLEUSEREXTENSIONID as ExtID
, sum(Duration) as Total_Talk_Time_seconds
, round(sum(Duration) / 60,2) as Total_Talk_Time_minutes
, sum(if(LEGTYPE1 = 1,1,0)) as Total_Outbound   
, sum(if(LEGTYPE1 = 2,1,0)) as Total_Inbound
, sum(if(Answered = 1,0,1)) as Missed_Calls
, count(DISTINCT b.NOTABLECALLID) as Total_Calls
, NOW()
, curdate()
FROM cdrdb.session a
LEFT JOIN cdrdb.callsummary b
        ON a.NOTABLECALLID = b.NOTABLECALLID
LEFT join cdrdb.mxuser c
      ON a.RESPONSIBLEUSEREXTENSIONID = c.EXTENSIONID
      CROSS JOIN (SELECT @id := 0 ) as var
WHERE b.ts >= curdate()
AND c.extension IN (7295,7306,7218,7247,7330,7000,7358)
group by c.extension;

Is there a way to use row count or something in my select statement so that my view has unique row IDs?

Upvotes: 0

Views: 74

Answers (1)

Juan Carlos Oropeza
Juan Carlos Oropeza

Reputation: 48177

Use user variables

 SELECT @id := @id + 1 as id, <other field>
 FROM ( <your tables> )
 CROSS JOIN (SELECT @id := 0 ) as var

Upvotes: 1

Related Questions