List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:July 7 1999 11:43am
Subject:Re: Select statement for the following...
View as plain text  
On Mi, 1999-07-07 09:48:25 +0000, Matt Duggan wrote:
> I have a table in my database that has two fields that I am 
> interested in, rowid and seq.
> 
> rowid is an auto-increment and seq is a sequential counter.
> 
> These relate to the object as follows...
> 
> OBJECT has both a rowid and the seq id, i.e. 
> 
> OBJECT rowid=1156, seq=1
> SAMEOBJECT rowid=1164 seq=2
> SAMEOBJECT rowid=1201 seq=3
> 
> Now, I need to be able to pull out the rowid matching the highest
> seq value for any given object. 
> 
> I was thinking of using a php3 look to find it, like a 'while seq' 
> sort of thing but there must be an sql statement that can do the same
> thing, something like.
> 
> select rowid from tablename where seq=MAX(seq);

If you're really only interested in the two field rowid and seq,
this should work:

  SELECT rowid
  FROM yourtable
  ORDER BY seq DESC
  LIMIT 1;

But I guess you have at least one more column holding the object id.
Then we extend the query:

  SELECT rowid
  FROM yourtable
  WHERE oid='xyz'
  ORDER BY seq DESC
  LIMIT 1;

The problem becomes more complex, if you not only want a result for a
single given object but a table over all objects at once.

WRONG query:

 SELECT rowid, MAX(seq)
 FROM yourtable
 GROUP BY oid;

 Standard SQL doesn't allow to SELECT "rowid" if it's not in the
 GROUP BY clause, because it would be not clear then which of the
 rows within each group to choose.

I just found a work-around, but it's ugly and most probably not
very efficient:

  By combining all relevant columns into a single value, I can
  keep the record together while using MAX and GRUOP BY.  Then,
  if MAX has selected the relevant row of each group, I again
  split the combined value into it's parts for output.

Base idea without resplitting:

  SELECT MAX( CONCAT(seq, '.', rowid) ) AS seq_rowid
  FROM yourtable
  GROUP BY oid;

And now with splitting the seq_rowid again:

  SELECT
    SUBSTRING_INDEX(MAX(CONCAT(seq, '.', rowid)),'.',-1) AS rowid,
    SUBSTRING_INDEX(MAX(CONCAT(seq, '.', rowid)),'.', 1) AS seq,
  FROM yourtable
  GROUP BY oid;

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
Thread
ram: 1 gig or 2 ?matthew mcglynn7 Jul
  • Re: ram: 1 gig or 2 ?Patrick Greenwell7 Jul
  • Re: ram: 1 gig or 2 ?jonathan michaels7 Jul
  • Re: ram: 1 gig or 2 ?CacoDem0n7 Jul
    • Re: ram: 1 gig or 2 ?Orlando Andico7 Jul
  • Select statement for the following...Matt Duggan7 Jul
    • Re: Select statement for the following...Martin Ramsch7 Jul
  • Re: ram: 1 gig or 2 ?Orlando Andico7 Jul
    • Re: ram: 1 gig or 2 ?Greg Patterson7 Jul
      • Re: ram: 1 gig or 2 ?Patrick Greenwell7 Jul
        • Re: ram: 1 gig or 2 ?Junster7 Jul
        • Re: ram: 1 gig or 2 ?(Chris Adams)7 Jul
      • Re: ram: 1 gig or 2 ?Orlando Andico7 Jul
        • Re: ram: 1 gig or 2 ?mab7 Jul
          • Re: ram: 1 gig or 2 ?Orlando Andico7 Jul
Re: ram: 1 gig or 2 ?Richard McLean7 Jul
  • Re: ram: 1 gig or 2 ?Orlando Andico7 Jul
  • Re: ram: 1 gig or 2 ?Paul DuBois7 Jul