List:General Discussion« Previous MessageNext Message »
From:Mark Jones Date:August 15 2002 1:43am
Subject:Re: query quandary
View as plain text  
Thank you!!!  It works!!

I'm curious how the creation of the temporary table would behave in a web
server environment.  At which point does mysql delete the temp table?  After
the db connection instituted by the script disconnects (in my case, after
the Perl DBI database handle is disconnected)?

Again, many flowers.

Mark Jones

----- Original Message -----
From: "DL Neil" <PHPml@stripped>
To: "Mark Jones" <msibleyj@stripped>
Cc: <mysql@stripped>
Sent: Tuesday, August 13, 2002 9:44 AM
Subject: Re: query quandary


> 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