How to get unique records with join tables and without using group by

41
February 12, 2019, at 07:40 AM

I want to get unique record from 2 tables without using Group By instead of i want to use DISTINCT because when i used group by it does not return last updated records.

I used following query with Group By.

SELECT  au.id, o.order_id,o.payment_method, o.order_date,au.article_id FROM `orders` as o INNER JOIN austpost_api_response as au ON  o.order_id = au.order_id  where o.shipment_status = 1 AND au.active_status = 1 AND o.order_status = 0 group by o.order_id
ORDER BY `au`.`id`  DESC

It's returns unique order_id but not getting last updated order_id

For example...

Order number 1212 has four article_id id 45,76,47,48 but once i used this with group by it's display unique order id but not getting 48 last updated record instead of it's return 45.

is it possible to get with DISTINCT keyword.

Answer 1

You can use row_number() function if your mysql version 8.0+

select * from
(
SELECT  au.id, o.order_id,o.payment_method, o.order_date,au.article_id,row_number() over(partition by o.order_id order by o.order_date desc) as rn
FROM `orders` as o INNER JOIN austpost_api_response as au ON  o.order_id = au.order_id  where o.shipment_status = 1 AND au.active_status = 1 AND o.order_status = 0 
)A where rn=1

OR You can try using correlated subquery

SELECT  au.id, o.order_id,o.payment_method, o.order_date,au.article_id
FROM `orders` as o INNER JOIN austpost_api_response as au ON  o.order_id = au.order_id  
where o.shipment_status = 1 AND au.active_status = 1 AND o.order_status = 0 and o.order_date in 
(
  select max(o.order_date) from `orders` o1 where o.order_id = o1.order_id and o1.order_status = 0
)
Answer 2

If I understand correctly, you have multiple rows for the responses for each order -- and you want the most recent one per order.

You want to filter the data, not aggregate it. I think this does what you want:

SELECT au.id, o.order_id, o.payment_method, o.order_date, au.article_id
FROM orders o INNER JOIN
     austpost_api_response au
     ON o.order_id = au.order_id  
WHERE o.shipment_status = 1 AND
      au.active_status = 1 AND
      o.order_status = 0 AND
      au.id = (SELECT MAX(au2.id)
               FROM austpost_api_response au2
               WHERE au2.order_id = au.order_id AND
                     au2.active_status = 1
              )
ORDER BY au.id DESC;
READ ALSO
Convert NOT IN to LEFT JOIN

Convert NOT IN to LEFT JOIN

I have this query:

68
How take record with last date in MySQL?

How take record with last date in MySQL?

In MySQL database I have table with such structure:

56
Insert multiple Scrapy data into mysql

Insert multiple Scrapy data into mysql

I have done to scrapy one of website then it also able to adapt to multiple pageBut when I want to insert into database, there is difficulty that I dont know

34
Adding a mysql datasource to jboss AS 7

Adding a mysql datasource to jboss AS 7

I am trying to add a MySQL data source to JBoss AS 71

42