Combining two MySQL queries from the same table

Question

I have a table set up currently as such:

issue|total   | series
-----|--------|---------
1    | 1      | Series A
2    | 2      | Series A
1    | 3      | Series B
3    | 4      | Series A
2    | 5      | Series B
1    | 6      | Series C

It tracks series (series), along with the issue number (issue) within each series. As well as that, I have a running total (total) of the entire number of entries in this table.

What I would like to do is combine two queries, the first used to locate the last issue number within a given series, the second to always locate the last total number within the table. Presumably the two queries I need to use are as follows, I just don't see how would be best to combine them into one select query:

To locate last issue of a given series:

SELECT issue FROM seriesTable WHERE series = 'Series A|Series B|Series C' ORDER BY issue DESC LIMIT 1

To locate total number of entries:

SELECT total FROM seriesTable ORDER BY total DESC LIMIT 1

So, if I wanted to reference the last issue of Series A and the total number of entries (3, 6), what would be the best way to do this? I've tried INNER JOIN and UNION neither of which seem to work, however I think I'm on the right track, just using the syntax incorrectly.

I guess the other alternative is to run the queries separately, add to an array and have PHP give me the result, however I would rather avoid this if possible.

Solution 1

Original answer is below, after clarification I have have another answer

You only need to cross join a single row result set with the total you're fater.

for series A

SELECT series, MAX(issue), total.total FROM seriesTable
  JOIN (SELECT total FROM seriesTable ORDER BY total DESC LIMIT 1) total
 WHERE series = 'Series A';

or for every series

SELECT series, MAX(issue), total.total FROM seriesTable
  JOIN (SELECT total FROM seriesTable ORDER BY total DESC LIMIT 1) total
 GROUP BY series;

sql fiddle

Original answer

So, to get "total for each series as of the last issue for that series" you need to self join and group by series to find the max issue, then use that as you join to extract the total.

select issue as current_issue, total, series from seriesTable
natural join (SELECT series, 
                     max(issue) as issue 
                FROM seriesTable 
               group by series) max_issues;

sql fiddle

Solution 2

You just need the max issue for a particular series, and you also need the max total for the table.

I think this is what your looking for:

SELECT max(issue), 
(SELECT max(total) 
    FROM seriesTable) 
    FROM seriesTable 
    WHERE series = 'Series A'

Comments

[Andreas Wederbrand]

how would you like your output to look?

[Ryan]

This is very succinct, however when I run this query, I get the same result for MAX(issue) and COUNT(issue) - i.e. in the example above, for Series A I get (3,3). What I'm trying to do is get (3,6), i.e. the last Series A number and the total number of entries in the entire table.

[Ryan]

When I run this query, I appear to get the number in the 'total' column that is associated with that issue number. If searching for Series A, I'd like to get (3,6), i.e. the final Series A issue and the total number of entries. If searching Series B, I'd want (2,6), i.e. the last Series B issue plus the total number of entries.

[Ryan]

This works perfectly. I confess, I don't understand the logic, but it appears to be doing exactly what I need it to.

[DragonZero]

try this: select max(issue), (select max(total) from seriesTable) from seriesTable where series = 'Series A'

[Parag Tyagi]

Got some great idea from your query. Thanks a ton. +1.