Styjun

Wordplay subtraction paradox

Why doesn't philosophy have higher standards for its arguments?

What "fuel more powerful than anything the West (had) in stock" put Laika in orbit aboard Sputnik 2?

Interviewing with an unmentioned 9 months of sick leave taken during a job

Why does "git status" show I'm on the master branch and "git branch" does not in a newly created repository?

How could an animal "smell" carbon monoxide?

What is the difference between a Hosaka, Ono-Sendai, and a "deck"?

Why does FFmpeg choose 10+20+20 ms instead of an even 16 ms for 60 fps GIF images?

Intel 8080-based home computers

ROT13 encoder/decoder

Is it ethical for a company to ask its employees to move furniture on a weekend?

Random piece of plastic

What are "full piece" and "half piece" in chess?

Was Jacobi the first to notice the ambiguity in the partial derivatives notation? And did anyone object to his fix?

Kepler space telescope undetected planets

Strategy to pay off revolving debt while building reserve savings fund?

Increasing muscle power without increasing volume

Pi 3 B+ no audio device found

Is this Android phone Android 9.0 or Android 6.0?

My credit card has no magnetic stripe. Is this a problem in the USA?

How to make a plagal cadence sound convincing as an ending?

Is straight-up writing someone's opinions telling?

Get node ID or URL in Twig on field level

what relax command means?

Optimize query of mysql

How to output MySQL query results in CSV format?How do I connect to a MySQL Database in Python?Should I use the datetime or timestamp data type in MySQL?Optimize 2 mysql queries to oneHow to get a list of user accounts using the command line in MySQL?Optimize SQL QueryHow to reset AUTO_INCREMENT in MySQL?How do I import an SQL file using the command line in MySQL?MySQL Query to select between 2 datesOptimizing MySQL query with multiple joins and Sub query

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty margin-bottom:0;

0

In current scenario, My Database store each user order item, Where one order have multiple items and all items will have same orderNumber. I am not much experienced in mysql. But I had modified the default search query to get the order by auditTimestamp. As user can edit order items, Which will update the single product auditTimestamp.

I have created an inner query earlier to iterate all orders and then group by them, After that get their latest auditTimestamp using MAX function. Currently I need to optimize it. below is the sample. Where I need to optimize the inner join.

select
 distinct ORDER.ORDERID
 ,ORDER.ORDERNUMBER
 ,ORDER.CREATIONDATE
 ,ORDER.STATUS 
 ,ORDER.AUDITTIMESTAMP
 ,ORDER.USERID
 ,ORDER.SEQ
 ,maxaudit.maxaudittimestamp as maxaudittimestamp
 from
 ORDER as ORD
 INNER JOIN (select
 ORDER.ORDERID
 ,ORDER.ORDERNUMBER
 ,ORDER.CREATIONDATE
 ,ORDER.STATUS 
 ,ORDER.AUDITTIMESTAMP
 ,ORDER.USERID
 ,ORDER.SEQ
 ,MAX(ORD1.AUDITTIMESTAMP) maxaudittimestamp
 from
 ORDER ORD1
 where
 ORD1.USERID = 1 AND
 ORD1.STATUS IN ('Active'
 ,'CANCELLED'
 ,'OUT FOR DELIVERY'
 ,'Void')
 GROUP BY
 ORD1.ORDERNUMBER) maxaudit
 on maxaudit.ORDERID = ORD.ORDERID
 WHERE
 WAR.ORDERID = 1) ORRDATA
 WHERE 1 = 1 AND
 ,ORDER.SEQ = 1
ORDER BY
 ORRDATA.maxaudittimestamp DESC;

As Result I need to get All orde by user and sort them by latest first. But whose primary order of seq = 1, status should be 'Active'
,'CANCELLED'
,'OUT FOR DELIVERY'
,'Void'

Optimisation required to replace

mysql

share|improve this question

asked Mar 26 at 9:16

SurendraKumar JaiswalSurendraKumar Jaiswal

1091414 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Wondering how this can be working as "ORDER" is a reserved word and you don't quote it
  
  – B001ᛦ
  Mar 26 at 9:18
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Make your live easier . Dont use reseverd keyword for schemas and fieldnames
  
  – Bernd Buffen
  Mar 26 at 9:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @B001ᛦ real query is working fine, this is just an dummy example. The real object is to optimise the inner query with inner join, When I try to use inner join, so while writting group by it gives error. So am stuck .
  
  – SurendraKumar Jaiswal
  Mar 26 at 10:38
  

  
  
  
  

add a comment | 

0

In current scenario, My Database store each user order item, Where one order have multiple items and all items will have same orderNumber. I am not much experienced in mysql. But I had modified the default search query to get the order by auditTimestamp. As user can edit order items, Which will update the single product auditTimestamp.

I have created an inner query earlier to iterate all orders and then group by them, After that get their latest auditTimestamp using MAX function. Currently I need to optimize it. below is the sample. Where I need to optimize the inner join.

select
 distinct ORDER.ORDERID
 ,ORDER.ORDERNUMBER
 ,ORDER.CREATIONDATE
 ,ORDER.STATUS 
 ,ORDER.AUDITTIMESTAMP
 ,ORDER.USERID
 ,ORDER.SEQ
 ,maxaudit.maxaudittimestamp as maxaudittimestamp
 from
 ORDER as ORD
 INNER JOIN (select
 ORDER.ORDERID
 ,ORDER.ORDERNUMBER
 ,ORDER.CREATIONDATE
 ,ORDER.STATUS 
 ,ORDER.AUDITTIMESTAMP
 ,ORDER.USERID
 ,ORDER.SEQ
 ,MAX(ORD1.AUDITTIMESTAMP) maxaudittimestamp
 from
 ORDER ORD1
 where
 ORD1.USERID = 1 AND
 ORD1.STATUS IN ('Active'
 ,'CANCELLED'
 ,'OUT FOR DELIVERY'
 ,'Void')
 GROUP BY
 ORD1.ORDERNUMBER) maxaudit
 on maxaudit.ORDERID = ORD.ORDERID
 WHERE
 WAR.ORDERID = 1) ORRDATA
 WHERE 1 = 1 AND
 ,ORDER.SEQ = 1
ORDER BY
 ORRDATA.maxaudittimestamp DESC;

As Result I need to get All orde by user and sort them by latest first. But whose primary order of seq = 1, status should be 'Active'
,'CANCELLED'
,'OUT FOR DELIVERY'
,'Void'

Optimisation required to replace

mysql

share|improve this question

asked Mar 26 at 9:16

SurendraKumar JaiswalSurendraKumar Jaiswal

1091414 bronze badges

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Wondering how this can be working as "ORDER" is a reserved word and you don't quote it
  
  – B001ᛦ
  Mar 26 at 9:18
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  Make your live easier . Dont use reseverd keyword for schemas and fieldnames
  
  – Bernd Buffen
  Mar 26 at 9:45
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  @B001ᛦ real query is working fine, this is just an dummy example. The real object is to optimise the inner query with inner join, When I try to use inner join, so while writting group by it gives error. So am stuck .
  
  – SurendraKumar Jaiswal
  Mar 26 at 10:38
  

  
  
  
  

add a comment | 

In current scenario, My Database store each user order item, Where one order have multiple items and all items will have same orderNumber. I am not much experienced in mysql. But I had modified the default search query to get the order by auditTimestamp. As user can edit order items, Which will update the single product auditTimestamp.

I have created an inner query earlier to iterate all orders and then group by them, After that get their latest auditTimestamp using MAX function. Currently I need to optimize it. below is the sample. Where I need to optimize the inner join.

select
 distinct ORDER.ORDERID
 ,ORDER.ORDERNUMBER
 ,ORDER.CREATIONDATE
 ,ORDER.STATUS 
 ,ORDER.AUDITTIMESTAMP
 ,ORDER.USERID
 ,ORDER.SEQ
 ,maxaudit.maxaudittimestamp as maxaudittimestamp
 from
 ORDER as ORD
 INNER JOIN (select
 ORDER.ORDERID
 ,ORDER.ORDERNUMBER
 ,ORDER.CREATIONDATE
 ,ORDER.STATUS 
 ,ORDER.AUDITTIMESTAMP
 ,ORDER.USERID
 ,ORDER.SEQ
 ,MAX(ORD1.AUDITTIMESTAMP) maxaudittimestamp
 from
 ORDER ORD1
 where
 ORD1.USERID = 1 AND
 ORD1.STATUS IN ('Active'
 ,'CANCELLED'
 ,'OUT FOR DELIVERY'
 ,'Void')
 GROUP BY
 ORD1.ORDERNUMBER) maxaudit
 on maxaudit.ORDERID = ORD.ORDERID
 WHERE
 WAR.ORDERID = 1) ORRDATA
 WHERE 1 = 1 AND
 ,ORDER.SEQ = 1
ORDER BY
 ORRDATA.maxaudittimestamp DESC;

As Result I need to get All orde by user and sort them by latest first. But whose primary order of seq = 1, status should be 'Active'
,'CANCELLED'
,'OUT FOR DELIVERY'
,'Void'

Optimisation required to replace

mysql

share|improve this question

asked Mar 26 at 9:16

SurendraKumar JaiswalSurendraKumar Jaiswal

1091414 bronze badges

select
 distinct ORDER.ORDERID
 ,ORDER.ORDERNUMBER
 ,ORDER.CREATIONDATE
 ,ORDER.STATUS 
 ,ORDER.AUDITTIMESTAMP
 ,ORDER.USERID
 ,ORDER.SEQ
 ,maxaudit.maxaudittimestamp as maxaudittimestamp
 from
 ORDER as ORD
 INNER JOIN (select
 ORDER.ORDERID
 ,ORDER.ORDERNUMBER
 ,ORDER.CREATIONDATE
 ,ORDER.STATUS 
 ,ORDER.AUDITTIMESTAMP
 ,ORDER.USERID
 ,ORDER.SEQ
 ,MAX(ORD1.AUDITTIMESTAMP) maxaudittimestamp
 from
 ORDER ORD1
 where
 ORD1.USERID = 1 AND
 ORD1.STATUS IN ('Active'
 ,'CANCELLED'
 ,'OUT FOR DELIVERY'
 ,'Void')
 GROUP BY
 ORD1.ORDERNUMBER) maxaudit
 on maxaudit.ORDERID = ORD.ORDERID
 WHERE
 WAR.ORDERID = 1) ORRDATA
 WHERE 1 = 1 AND
 ,ORDER.SEQ = 1
ORDER BY
 ORRDATA.maxaudittimestamp DESC;

share|improve this question

asked Mar 26 at 9:16

SurendraKumar JaiswalSurendraKumar Jaiswal

1091414 bronze badges

share|improve this question

asked Mar 26 at 9:16

SurendraKumar JaiswalSurendraKumar Jaiswal

1091414 bronze badges

asked Mar 26 at 9:16

SurendraKumar JaiswalSurendraKumar Jaiswal

1091414 bronze badges

asked Mar 26 at 9:16

SurendraKumar JaiswalSurendraKumar Jaiswal

1091414 bronze badges