开发者

Selecting closest surrounding rows (above/below) with SQL from a MySQL database resultset

开发者 https://www.devze.com 2023-04-13 09:48 出处:网络
I\'m trying to retrieve the closest adjacent rows (above and below) a record in a result set that match a certain condition.

I'm trying to retrieve the closest adjacent rows (above and below) a record in a result set that match a certain condition.

My problem appears to be in my use of min and max here:

WHERE   ma_c.id = 2 
            AND ma_e.id > 4 
            AND ma_e.id = MIN(ma_e.id))

The error I receive is: error : Invalid use of group function

Not quite sure what I'm doing incorrectly

    (SELECT     
        ma_c.id                 as contest_id,
        ma_c.name               as name,
        ma_c.title              as title,
        ma_u.id                 as user_id,
        ma_u.first_name         as user_first_name,
        ma_u.last_name          as user_last_name,
        ma_u.street_address     as user_street_address,
        ma_u.city               as user_city,
        ma_u.zip_code           as user_zip_code,
        ma_u.email              as user_email,
        ma_u.phone_number       as user_phone_number,
        ma_u.country_code       as user_country_code,
        ma_u.gender             as user_gender,
        ma_u.dob                as user_dob,
        ma_u.ssn                as user_ssn,
        ma_u.canadian_province  as user_canadian_province,
        ma_u.state              as user_state,
        ma_e.id                 as entry_id,
        ma_e.hash_id            as entry_hash_id,
        ma_e.create_datetime    as entry_create_datetime,
        ma_e.entry_type         as entry_type,
        ma_e.title              as entry_title
    FROM    ma_contests ma_c
    JOIN    ma_users ma_u
    ON      ma_c.id = ma_u.contests_id

    JOIN    ma_entries ma_e
    ON      ma_u.id = ma_e.users_id

    WHERE   ma_c.id = 2 
            AND ma_e.id > 4 
            AND ma_e.id = MIN(ma_e.id))

UNION

    (SELECT     
        ma_c.id                 as contest_id,
        ma_c.name               as name,
        ma_c.title              as title,
        ma_u.id                 as user_id,
  开发者_Python百科      ma_u.first_name         as user_first_name,
        ma_u.last_name          as user_last_name,
        ma_u.street_address     as user_street_address,
        ma_u.city               as user_city,
        ma_u.zip_code           as user_zip_code,
        ma_u.email              as user_email,
        ma_u.phone_number       as user_phone_number,
        ma_u.country_code       as user_country_code,
        ma_u.gender             as user_gender,
        ma_u.dob                as user_dob,
        ma_u.ssn                as user_ssn,
        ma_u.canadian_province  as user_canadian_province,
        ma_u.state              as user_state,
        ma_e.id                 as entry_id,
        ma_e.hash_id            as entry_hash_id,
        ma_e.create_datetime    as entry_create_datetime,
        ma_e.entry_type         as entry_type,
        ma_e.title              as entry_title
    FROM    ma_contests ma_c
    JOIN    ma_users ma_u
    ON      ma_c.id = ma_u.contests_id

    JOIN    ma_entries ma_e
    ON      ma_u.id = ma_e.users_id

    WHERE   ma_c.id = 2 
            AND ma_e.id < 4 
            AND ma_e.id = MAX(ma_e.id))

-----EDIT---- This is the relevant schema uml for the query I'm attempting to execute

Selecting closest surrounding rows (above/below) with SQL from a MySQL database resultset


The MAX() is an aggregate function, basically one expected to look through up a bunch of rows and give you an single answer per grouping element.

If you want to know the MIN or MAX from the ma_entries table, you need to add the following JOIN be

JOIN    ma_entries ma_e     ON      ma_u.id = ma_e.users_id      
JOIN (select min(id) as SmallestID FROM ma_entries) xx ON 1=1

WHERE   ma_c.id = 2             
        AND ma_e.id > 4             
        AND ma_e.id = xx.SmallestID

You could use a similar approach for the MAX() id...

However, I don't think you WHERE clause is right, because you are asking for rows with an ID above a particular value AND = a particular value. I suggest reviewing your where clause


SELECT     
        ma_c.id                 as contest_id,
        ma_c.name               as name,
        ma_c.title              as title,
        ma_u.id                 as user_id,
        ma_u.first_name         as user_first_name,
        ma_u.last_name          as user_last_name,
        ma_u.street_address     as user_street_address,
        ma_u.city               as user_city,
        ma_u.zip_code           as user_zip_code,
        ma_u.email              as user_email,
        ma_u.phone_number       as user_phone_number,
        ma_u.country_code       as user_country_code,
        ma_u.gender             as user_gender,
        ma_u.dob                as user_dob,
        ma_u.ssn                as user_ssn,
        ma_u.canadian_province  as user_canadian_province,
        ma_u.state              as user_state,
        ma_e.id                 as entry_id,
        ma_e.hash_id            as entry_hash_id,
        ma_e.create_datetime    as entry_create_datetime,
        ma_e.entry_type         as entry_type,
        ma_e.title              as entry_title
    FROM    ma_contests ma_c
    JOIN    ma_users ma_u
    ON      ma_c.id = ma_u.contests_id

    JOIN    ma_entries ma_e
    ON      ma_u.id = ma_e.users_id

    WHERE   ma_c.id = 2 
            AND ma_e.id > 4 
            AND ma_e.id = (SELECT min(_ma_e.id)       
                            FROM    ma_contests _ma_c
                            JOIN    ma_users _ma_u
                            ON      _ma_c.id = _ma_u.contests_id

                            JOIN    ma_entries _ma_e
                            ON      _ma_u.id = _ma_e.users_id

                            WHERE   _ma_c.id = 2 
                                    AND _ma_e.id > 4)

and same for the next query

0

精彩评论

暂无评论...
验证码 换一张
取 消