How to determine the first result of a group in MySQL?

Question

So I'm doing a collectible cards managing app, and I have these tables:

• "card": contains all the distinct cards ever made, and gives their informations (id, name, text, and so on)
• "edition": contains all the card sets ever released (called it "edition" because "set" was a reserved word)
• "cardinset": since a card can appear in more than one set, this is the associative table between the two. It also gives the number of the card in the set and the number of copies I have of it in french (fr) and in english (en) from that set.

Of course, all tables have a unique auto-increment id, called "id".

The purpose of my SQL request is to list all the cards of a set, ordered by the number of the card in the set. I need all the info of the "cardinset" entry (number, fr, en) and of course the info of the "card" entry (name, rarity, etc.), but I also need to know how many copies of each card I have in total (across all sets, not just in this one).

My SQL request looks like this (I removed a few fields that weren't important):

SELECT
    c.name,
    c.rarity,
    SUM(cis.fr) + SUM(cis.en) AS available,
    cis.number,
    cis.fr,
    cis.en
FROM
    card AS c 
    INNER JOIN cardinset AS cis ON c.id = cis.cardId 
WHERE
    c.id IN
    ( 
        SELECT
            cardId 
        FROM
            cardinset AS cs 
        WHERE
            setId = 104 
        ORDER BY
            number
    ) 
GROUP BY
    c.id,
    c.name 
ORDER BY
    cis.number

It almost works, but it doesn't retrieve the right cardinset entry for each card, since it takes the first one of the group, which is not always the one linked to the right set.

Example:

| c.name         | c.rarity | available | cis.number | cis.fr | cis.en |
| -------------- | -------- | --------- | ---------- | ------ | ------ | 
| Divine Verdict | Common   | 9         | 008        | 1      | 1      |

Here, the card info (name and rarity) are correct, as well as the "available" field. However the cis field are wrong: they are part of a cis entry linking this card to another set.

The question is: is it possible to define which entry is the first in the group, and therefore is returned in this case? And if not, is there another way (maybe cleaner) to get the result I want?

Thank you in advance for your answer, I really don't know what to do here... I guess I've reached the limits of my knowledge of MySQL...

---

Here's a more precise example. This screenshot n°1 shows the first results of my query (described above), knowing that there are 212 results in total. They should be ordered by number, and there should be exactly one result of each number, and yet there are some exceptions:

n° 005, which should be "Divine Verdict" isn't there, because it appears instead as n° 008. That's because that card is part of 6 different sets, a we can see in screenshot n°2 (result of the query "SELECT * FROM cardinset WHERE cardId = 13984"), and the group returns the first entry, which is for set n°12 and not n°104 as I would have it. However the "available" field shows "9", which is the result I want: the sum of all the "fr" and "en" field for that card in all 6 sets it appears in.

There are other cards that don't have the right cardinset info: n° 011 and 019 are missing, but can be found lower with other cardinset info.

Answer 1

I made it at last! I used a subquery to get the "available" field with a GROUP BY clause. It's long, and not very fast, but it gets the job done. If you have an idea that could improve it, don't hesitate.

SELECT e.code, cs.number, sub.name, sub.rarity, cs.fr, cs.en, sub.available
FROM cardinset as cs
INNER JOIN edition as e ON e.id = cs.setId
INNER JOIN (
    SELECT c.id, c.name, c.rarity,
        SUM(cis.fr)+SUM(cis.en) as available, SUM(cis.frused)+SUM(cis.enused) as used
    FROM card as c
    INNER JOIN cardinset as cis ON c.id = cis.cardId 
    WHERE c.id IN ( 
        SELECT cardId 
        FROM cardinset as cins
        WHERE setId = 54)
    GROUP BY c.id, c.name
    ORDER BY c.id
    ) AS sub ON cs.cardId = sub.id
WHERE setId = 54
ORDER BY cs.number

Answer 2

I believe this is the way you would want to format your query.

SELECT
    c.name,
    c.rarity,
    cis.fr + cis.en AS available,
    cis.number,
    cis.fr,
    cis.en
FROM
    card AS c 
INNER JOIN cardinset AS cis ON c.id = cis.cardId 
WHERE
c.id IN
( 
    SELECT
        cardId 
    FROM
        cardinset AS cs 
    WHERE
        setId = 104 
    GROUP BY
        setID, cardID
) 
ORDER BY
    cis.number

The GROUP BY clause was moved into the sub select and modified to make sure an entry is the right combo of card/set. Also removed the SUMs because that was not necessary.