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
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
精彩评论