[thelist] sql query help

Sean German ethanol at mathlab.sunysb.edu
Fri May 24 10:30:14 CDT 2002


Chris,

That's a good one.  What you need is the article_id where the sort_order
is 1 and there are more than one records with that sort_order.  Part of
the issue is joining the table with itself to compare sort_orders.

I'm thinking something along the lines of:

SELECT article_id
FROM paragraphs
WHERE (SELECT count(sort_order)
       FROM paragraphs as a, paragraphs as b
       WHERE a.article_id = b.article_id
       AND a.sort_order = b.sort_order) > 1
/* more than one record with equal article_id and equal sort_order
AND sort_order =1
/* that sort_order should be 1


Or something along those lines.


HTH  =)


Sean G.


--- Chris Blessing <webguy at mail.rit.edu> wrote:
> myTable: article_id, paragraph_id, sort_order
>
> There can be multiple paragraphs per article, and they are ordered
(through
> the article) by sort_order.
>
> Now for some reason the program I used to import a bunch of articles has
> given a lot of these articles non-incremental sort_orders, so one
article
> might show up like this:
>
> 1,1,1
> 1,2,1
> 1,3,1
> 1,4,1
>
> So article 1 has 4 paragraphs, and they're all sorted as paragraph #1.
This
> is no good for obvious reasons! ;)
>
> What I need to do is get a list of all the articles (article_id for
each)
> which have this problem (the sort_order being 1 and only 1).  Trouble is
> that EVERY article in the table (all 5300 of them) has at least one
> sort_order = 1, so I need to single out the articles that ONLY have a
> sort_order of 1 for every paragraph.
>
> I thought something like this might work but it just returns some
arbitrary
> number of articles...
>
> select article_id from paragraphs
> where sort_order = 1
> group by article_id
> having count(article_id) > 1
>





More information about the thelist mailing list