Question 112804 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql data extraction

I have a table called deals, it has records like this for example

id deal_ref objectname  status
1  1234      tom        correct
2  1234      tom        correct
3  1234      jerry      wrong
4  1234      tom        correct

I need to identify all latest deals where the status is "correct for example, but the last entry(row 4) must meet the following criteria, where the Max ID is equal to the deal_ref and the status is correct

I tried this

select  distinct deal_ref, deal_status
from    dealv1 d 
where   d.deal_ref = max(id) 
and  d.deal_status  = 'Prospect'  
and date_created between '2022-11-02 00:00:00' and '2022-11-04 00:00:00'
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Guys the query below worked, but its displaying multiple deal_refs that has the same deal_ref, for example 2 rows below each other with deal_ref 1234 twice.

    SELECT *
FROM dealv1 d
WHERE status = 'correct'
ORDER BY ID DESC
LIMIT 1

  2. 0

    You use other names in your SQL than in the table (deal_status, date_created).
    Nevertheless try do it the following:

    SELECT *
FROM dealv1 d
WHERE status = 'correct'
ORDER BY ID DESC
LIMIT 1

    i don´t get exactly what you are trying to do with the maxID. You just want the one row where deal_ref=max(id) and status is correct?
    Then add

    AND deal_ref = (SELECT MAX(id) from dealv1)

    after ‘correct’ from the above statement.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search