CODEWITHSUNDEEP
mysql
Perocat
Perocat

Reputation: 1521

Select last date and all dates in the future

I'd like to select only the nearest date in the past and all the dates in the future.

I reach a result with the following query, but the results are side by side instead row per row.

How should I modifiy my query?

SELECT t1.*, t2.*
FROM
    (SELECT *
        FROM table1
     WHERE from_p <= NOW()
        AND prod = 3000
     ORDER BY from_p DESC
     LIMIT 1) AS t1
JOIN
    (SELECT *
        FROM table1
     WHERE from_p >= NOW()
        AND prod = 3000
     ORDER BY from_p DESC
    ) AS t2

Upvotes: 1

Views: 70

Answers (2)

pmoo
pmoo

Reputation: 331

Try:

(SELECT *
    FROM table1
 WHERE from_p <= NOW()
    AND prod = 3000
 ORDER BY from_p DESC
 LIMIT 1)
UNION
 (SELECT *
    FROM table1
 WHERE from_p >= NOW()
    AND prod = 3000
 ORDER BY from_p DESC)

Upvotes: 0

msound
msound

Reputation: 445

You need a use a subquery to first find "latest past date" and then write the main query based on that:

SELECT * FROM table1 
WHERE from_p >= (
    SELECT from_p FROM table1 
    WHERE from_p <= NOW() AND prod=3000
   ORDER BY from_p DESC LIMIT 1
    )
    AND prod=3000 
ORDER BY from_p;

Upvotes: 1

Related Questions