MySQL Query Mystery

[willow_rd]

I need to do a MySQL query to find the most recent instance (date_field) for each (symbol_field) in a given (group_field). Additionally there is an (id_field). All fields are in one table. Any help would be appreciated.

An example group of records would be.........


____date __symbol____id____group

2004-12-18__XYZ __ 00222__ Building
2004-12-17__UVW __00224__ Building
2004-12-16__RST__ 00231__Building
2004-11-18__XYZ__00225__Building
2004-11-17__UVW__00219__Building
2004-11-16 __RST__00215__Building
2002-09-18__ ABC__ 00025__Building

The query would return...

2004-12-18__XYZ__00222__Building
2004-12-17__UVW__00224__Building
2004-12-16__RST__ 00231__Building
2002-09-18__ABC__00025__Building

Every time. Thanks.

[Boricua]

In SQL Server, the following query would give you what you need (if I understand your question):

SELECT *
 FROM table a
 INNER JOIN (SELECT date = MAX(date),
                             symbol,
                              group
            FROM table
            GROUP BY symbol, group
            ) b ON (a.date    = b.date    AND
                    a.symbol = b.symbol  AND
                    a.group   = b.group
                    )

In MySQL, I *think* the query can be rewritten like this:

SELECT *
 FROM table a
 INNER JOIN (SELECT date = MAX(date),
                             symbol,
                             group
              FROM table
              GROUP BY symbol, group
            ) as b ON (a.date    = b.date    AND
                       a.symbol = b.symbol  AND
                       a.group   = b.group
                       )

The only thing that's different in the second version is the "as b" part when aliasing the derived table.

[AndyH]

Is it just me or would this be what you need:

SELECT *
FROM `table`
ORDER BY `date` DESC;

I'm sure i'm missing something that makes it difficult...

[Blue Cat Buxton]

Andy

That will return all the Group info without sorting it.

[AndyH]

SELECT *
FROM `table`
GROUP BY `symbol_field`, `group_field`
ORDER BY `date` DESC;

???

[Boricua]

I think that for each symbol, he only wants the one associated with the last date. For example, if we reorder his source records and get rid of the ones that aren't the most recent (rows in blue), we end up with his results:

2004-12-18__XYZ __ 00222__ Building
2004-11-18__XYZ__00225__Building
2004-12-17__UVW __00224__ Building
2004-11-17__UVW__00219__Building
2004-12-16__RST__ 00231__Building
2004-11-16 __RST__00215__Building
2002-09-18__ ABC__ 00025__Building

So in my query above, the "derived table" subquery like this...

SELECT date = MAX(date),
                             symbol,
                              group
            FROM table
            GROUP BY symbol, group

Would return

2004-12-18__XYZ __ Building
2004-12-17__UVW__ Building
2004-12-16__RST__Building
2002-09-18__ABC__Building

That part is then joined back to the original table to get full rows of data.

Note that this approach wouldn't work if a symbol had multiple records on the same date. For example, if the source data had 2 records for "XYZ" on 2004-12-18, then the final result set would have dups (nevertheless, they could be removed if the date included a time component).

[willow_rd]

Sorry, I have been gone and won't be able to try things until the weekend. Below is what I use now. It works to get a set of all symbols for a group but doesn't always get the latest records for a symbol in a group. Confusing? It is to me too. Building queries is still Greek to me. Thanks.

SELECT symbol_f,company,pr,rec,group,MAX(l_date), DATE_FORMAT(ld,'%m/%d/%y'),lsne FROM table1 WHERE lsne = 1 or lsne = 8 GROUP BY symbol_F HAVING grp='$var_g' AND MAX(l_date) > '2003-01-00'