WHERE fieldname IS MIN(somedate) Is there a way to write this without using a subquery?

Question

Our server is having a hard time running this query. I was hoping someone could help me out by suggesting a way of re-writing it to get the same results without the sub query.

The purpose of the subquery is to use min in the where clause: I'd like only results returned with earliest co.created date.

So what I wish I could do is this:

SELECT 
        my.data AS Account_ID,
        co.ID AS User_ID,
        def.def_medium,
        co.created
FROM (tables with associated joins)
WHERE MIN(co.created)

But I cannot do that so I came up with this:

SELECT 
    my.data AS Account_ID,
    co.ID AS User_ID,
    def.def_medium,
    co.created
FROM
    abc_emails.cid208 co
INNER JOIN abc_emails.def208 def ON def.eid = co.id
INNER JOIN abc_emails.my208 my ON my.eid = co.id AND my.my_id = 2765
INNER JOIN (SELECT 
                my.data AS Account_ID,
                MIN(created) AS created
            FROM 
                abc_emails.my208 my
            INNER JOIN abc_emails.cid208 co ON co.id = my.eid
                WHERE my_id = 2765
            GROUP BY Account_ID
            ) b1 ON b1.Account_ID = my.data
GROUP BY Account_ID, User_ID, def_medium

But it's been running for a long time and, given my experience with this server, I'm about to get booted off with an error message telling me it's timed out.

Is it possible to do what I'm doing with no subquery?

@Strawberry how so? Are you referring to the top "query"? That's me just trying to write less and be more straight tot he point. My goal is to re-write the big query minus having to use b1 — Doug Fir, Dec 01 '14 at 23:06
Unless there are OLAP functions (RANK etc) in MySQL, then an explicit sub-query is necessary. With OLAP functions, you might be able to do it more slickly. — Jonathan Leffler, Dec 01 '14 at 23:10
Others may feel differently, but if I can't see the actual query (and more besides) then in most cases I cant help. So, if you like, consider following this simple two-step course of action: 1. If you have not already done so, provide proper DDLs (and/or an sqlfiddle) so that we can more easily replicate the problem. 2. If you have not already done so, provide a desired result set that corresponds with the information provided in step 1. — Strawberry, Dec 01 '14 at 23:31

score 1 · Accepted Answer · edited May 23 '17 at 11:50

1

Your Query you want to use:

SELECT 
        my.data AS Account_ID,
        co.ID AS User_ID,
        def.def_medium,
        co.created
FROM (tables with associated joins)
WHERE MIN(co.created)

Is not actually a query. Read it out loud to yourself. "I want this data, where the minimum date.....". It is an incomplete thought. You have to specify what you want to accomplish in the WHERE clause. It should read "I want this data, where the minimum date of the co table, is "

For example:

SELECT 
        my.data AS Account_ID,
        co.ID AS User_ID,
        def.def_medium,
        co.created
FROM (tables with associated joins)
WHERE my.date > MIN(co.created)

If you want only the oldest items, and to know their Account_id, User_Id, def_medium, You can just simply order the list by the created date ascendingly and grab the number of results you want.

SELECT  my.data AS Account_ID,
        co.ID AS User_ID,
        def.def_medium,
        co.created
FROM (tables with associated joins)
ORDER BY co.created ASC
LIMIT NumberOfResults

You're trying to find the oldest account_ID. If you have 10 rows, all with the same ID, you want to find the one with the MIN(co.created). That would be the equivalent of a MSSQL row_number partition: stackoverflow.com/questions/1895110/row-number-in-mysql

edited May 23 '17 at 11:50

Community

1
1

answered Dec 01 '14 at 23:09

DFTR

861
10
30

Ach! I got so excited when I saw "Top". But no, appears not to be a MySQL function – Doug Fir Dec 01 '14 at 23:33
Sorry, I was working on my own project and then came back to here and wrote MSSQL instead of MySQL by accident. I was in the middle of editing it when I got downvoted :/ – DFTR Dec 01 '14 at 23:36
OK but is there a way to apply LIMIT at the GROUP BY level and not the entire query? – Doug Fir Dec 01 '14 at 23:39
I think I finally know what you want. You're trying to find the oldest account_ID. If you have 10 rows, all with the same ID, you want to find the one with the MIN(co.created). Let me know if this helps: https://stackoverflow.com/questions/1895110/row-number-in-mysql – DFTR Dec 01 '14 at 23:44
It did help thanks for pointing me to that. I went with a NULL Self Left Join – Doug Fir Dec 02 '14 at 15:32
You're welcome. Just took me a while to decipher what you were trying to accomplish. – DFTR Dec 02 '14 at 15:45

WHERE fieldname IS MIN(somedate) Is there a way to write this without using a subquery?

1 Answers1