CODEWITHSUNDEEP
mysqlsql
Alex
Alex

Reputation: 19

First record and last query

With this query I display the year field, the first record in ASC order, and the last in DESC order.

Works well if the year field is different

example:

1960-1970

but if the field is the same

example:

1960

I see duplicate

1960 - 1960

I would only like to see once

1960

how to do?

$query = "(SELECT 
year as year_asc
FROM film_actor 
INNER JOIN film ON film_actor.film_id = film.film_id 
INNER JOIN actor ON film_actor.actor_id = actor.actor_id
WHERE film_actor.actor_id = $actor_id
ORDER BY year ASC
LIMIT 1)

UNION ALL

(SELECT 
year as year_desc
FROM film_actor 
INNER JOIN film ON film_actor.film_id = film.film_id 
INNER JOIN actor ON film_actor.actor_id = actor.actor_id
WHERE film_actor.actor_id = $actor_id
ORDER BY year DESC
LIMIT 1)";

Upvotes: 0

Views: 61

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269763

If you want the minimum and maximum years, you can put them in a single query:

SELECT MIN(year) as min_year, MAX(year) as max_year
FROM film_actor fa JOIN
     film f
     ON fa.film_id = f.film_id JOIN
     actor a
     ON fa.actor_id = a.actor_id
WHERE fa.actor_id = $actor_id;

If you want a single column, perhaps with a hyphen, you can use:

SELECT CONCAT_WS('-', MIN(YEAR), NULLIF(MAX(YEAR), MIN(YEAR)))

Upvotes: 0

Grace
Grace

Reputation: 888

you may use UNION, rather than UNION ALLsince UNION will eliminate duplicates.

Upvotes: 1

Related Questions