Mark,
I'm a great believer in 'divide and conquor', so let's take your clear
statement of problem and knowing that we can't achieve it by brute
force/direct attack, let's break it down and solve it a piece at a time:
table contains title and date of records
a) sort so that records are listed by title with the newest dates first
b) keeping records with identical titles together
c) order by date within the title groups.
Thanks for the data/sql, it saved me a bunch of time (which I could then
squander on figuring out a solution)
- I modified it somewhat to suit my db's/own naming conventions/avoid
clashes with other data, and to add the ID column (if only for
'documentation purposes'):
USE test;
DROP TABLE IF EXISTS markusers;
CREATE TABLE markusers
(
mark_title VARCHAR(50) NULL,
mark_date DATE NULL,
mark_id INTEGER
);
I replicated your query (below) and as expected MAX() works on the entire
table returning only the row containing the latest date.
If we group the data by title then instead of working on the whole table
MAX() is applied to each group in turn - I also changed the sequence because
I understand the term "newest dates" to mean "most recent" (please verify
that this agrees with your intent).
SELECT MAX(t1.mark_date) AS maxdate, t1.mark_title
FROM markusers AS t1
GROUP BY mark_title
ORDER BY maxdate DESC,
t1.mark_title DESC;
So now we appear to have specification (a) done. Let's start a fresh query
and tackle the next parts:
Keeping records/rows together is achieved by ordering the selection -
specification (b).
SELECT mark_date, mark_title
FROM markusers AS t2
ORDER BY mark_title;
Creating a secondary sequence within the above involves only extending the
order by clause to handle matches within the primary sequence - I made the
assumption that you would again want most recent dates first.
SELECT mark_title, mark_date
FROM markusers AS t2
ORDER BY mark_title ASC,
mark_date DESC;
So now we have two tables that approximate what you want - the first
organised by date and then title, the second organised by title and then
date. All we have to do is put them together!?
Unfortunately that is not as easy as it sounds. If you try to join the two
table formations (t1 and t2), what happens is that the GROUP BY clause ruins
everything (NB I did say "join to itself" earlier. I've struggled with it
for a while, but haven't managed to get my head around it***. Apologies), so
you have to put the early results - the maxdate priority out to a temporary
table, which can then be joined with the full table to produce the sequence
you require:
DROP TABLE IF EXISTS markpriority;
CREATE
TEMPORARY TABLE markpriority
SELECT mark_title
, MAX(mark_date) AS maxdate
FROM markusers
GROUP BY mark_title
ORDER BY maxdate DESC;
SELECT #t1.maxdate, t1.mark_title,
t2.mark_id, t2.mark_title, t2.mark_date
FROM markpriority AS t1
LEFT JOIN markusers AS t2
ON t1.mark_title = t2.mark_title
ORDER BY
t1.maxdate DESC
, t2.mark_title
, t2.mark_date DESC
Regards,
=dn
*** maybe someone else on the list can help us out?
> Thanks for your reply!
>
> I attempted the following query, but it only returned one row (the row
> matching the one latest date):
>
> select MAX(t1.date) as maxdate
> from testtable as t1
> left join testtable on t1.id=testtable.id
> order by maxdate asc,testtable.title desc;
>
> Not sure this is what you had in mind...
>
> The table definition and sample data files are attached.
>
> Thanks,
>
> Mark
>
> ----- Original Message -----
> > Mark,
> > Think it can be done by joining the table to itself and making the left
> side
> > of the join a MAX(date).
> > If that's not enough to get you going, please send me an 'export' of the
> > CREATE TABLE and 30~50 test rows of typical data, and I'll take a few
> > minutes out to have a 'play' and get back to you.
> > Regards,
> > =dn
> >
> > ----- Original Message -----
> > > I have a query I'm trying to construct on a table which contains a
title
> > and
> > > date of records. I want to sort the query so that records are listed
by
> > > title with the newest dates first--keeping the records with identical
> > titles
> > > together and order by date within the title groups.
> > >
> > > For example:
> > >
> > > Title 2, 10-30-2001
> > > Title 2, 10-15-2001
> > > Title 2, 10-01-2001
> > > Title 1, 10-20-2001
> > > Title 1, 10-10.2001
> > > Title 3, 10-05-2001
> > >
> > > My attempt at SELECT title, date FROM tablename GROUP BY title, date
> DESC
> > > produced an appropriately grouped result set, with the titles ordered
> > within
> > > the groups by date, BUT it put the groups in order alphabetically,
> whereas
> > > I'm wanting them ordered by the date of the first title in the groups.
> > >
> > > Any suggestions?
> > >
> > > Thanks,
> > >
> > > Mark Jones
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
