List:General Discussion« Previous MessageNext Message »
From:DL Neil Date:August 13 2002 2:44pm
Subject:Re: query quandary
View as plain text  
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


Thread
query quandaryMark Jones12 Aug
  • Re: query quandaryDL Neil13 Aug
  • Re: query quandaryMark Jones15 Aug
    • Re: query quandaryDan Nelson15 Aug