SQL从“所有时间记录”返回第一个记录日期

Question

Fishing Database that stores All-time records. However, when someone has a joint record, I want to revert to the first capture as the true record.

select * 
from T 
inner join (select Type, 
                   Name, 
                   max(TotDrams) as maxdrams 
            from T 
            WHERE Type='Common Bream' 
            group by Type, Name 
           ) sq on T.Type = sq.Type 
                and T.Name = sq.Name 
                and sq.maxdrams = T.TotDrams 
ORDER BY Ranking ASC

The above returns the all time records with the best capture for each name, but when a record is joint it naturally returns the additional record. I only want the earliest date record to be included in the all time records.

Is there a way of adapting the above code to remove the additional joint records and only pick the earliest recorded?

Fishname     Rank            Weight           Angler            Date

Slimey       Rank 1        2 lb   3 oz      John Budd         30/11/2013
Fishy        Rank 2        1 lb   15 oz     Chris Clot        12/01/2009
Scales       Rank 3        1 lb   12 oz     John Budd         21/03/2014
Scales       Rank 3        1 lb   12 oz     Harry White       01/04/2002

With the above example - which is what is currently happening, I would like to have John Budd's joint record removed as it is joint and is not the original.

One other note:- This SQL will be used with php.

Answer 1

The way I would approach this is slightly different, rather than trying to limit the results to the maximum, I would exclude the results that aren't. So using something like:

SELECT  T.*
FROM    T
        LEFT JOIN T AS T2
            ON T2.Species = T.Species
            AND T2.FishName = T.FishName
            AND (T2.TotalDrams < T.TotalDrams
                OR (T.TotalDrams = T2.TotalDrams AND T2.DateCaught > T.DateCaught))
WHERE   T.Species = 'Common Bream'
AND     T2.Species IS NULL
ORDER BY T.Rank ASC;

This uses the standard LEFT JOIN/IS NULL approach to exclude records where they are for the same fish type and either:

• Have a lower value for TotalDrams

OR

• Have the same value for TotalDrams AND a later DateCaught

Example on SQL Fiddle

Due to the way MySQL materialises sub-queries you might also find that this performs better than the original query that didn't give the required results!

---

EDIT

Okay, new approach. I think the best way to go about this is using variables to store a new Row number for each record, then you can filter for the top 1. The following will assign your row number based on ordering criteria:

SELECT  @r:= CASE WHEN @f = t.FishName AND @s:= t.Species 
                    THEN @r + 1 
                ELSE 1 
            END AS RowNum,
        @f:= t.FishName AS FishName,
        @s:= t.Species AS Species,
        t.Rank,
        t.Pounds,
        t.Ounces,
        t.Drams,
        t.TotalDrams,
        t.Peg,
        t.Angler,
        STR_TO_DATE(IF(t.DateCaught = '', '31/12/2050', t.DateCaught), '%d/%m/%Y')  AS DateCaught
FROM    T
        CROSS JOIN (SELECT  @f:= '',@s:='', @r:= 0) AS v
ORDER BY t.FishName, t.Species, t.TotalDrams DESC, DateCaught ASC;

Then you can put this into a subquery, and limit the records to the top 1:

SELECT  *
FROM    (   SELECT  @r:= CASE WHEN @f = t.FishName AND @s = t.Species 
                                THEN @r + 1 
                            ELSE 1 
                        END AS RowNum,
                    @f:= t.FishName AS FishName,
                    @s:= t.Species AS Species,
                    t.Rank,
                    t.Pounds,
                    t.Ounces,
                    t.Drams,
                    t.TotalDrams,
                    t.Peg,
                    t.Angler,
                    t.DateCaught
            FROM    T
                    CROSS JOIN (SELECT  @f:= '',@s:='', @r:= 0) AS v
            ORDER BY t.FishName, t.Species, t.TotalDrams DESC, STR_TO_DATE(IF(t.DateCaught = '', '31/12/2050', t.DateCaught), '%d/%m/%Y') ASC
        ) AS t
WHERE   t.RowNum = 1
ORDER BY t.Rank ASC;

Example on SQL Fiddle

This is the most flexible approach, if you want to add more rules, i.e. if you have two the same weight, and on the same date, you can add further ordering to your subquery, e.g. Angler. This guarantees only one record for each tuple of (fishname, species), and deterministic results given enough ordering.