Post reply

inner join, count

  • August 12, 2003 at 9:15 am

    #84113

    sSQL="SELECT Count(tblHits.ID) as HitCount,First(tblLinks.id) as fid, First(name) as fname, First(url) as furl, First(Description) as fdescription, First(date_added) as fdate_added, First(active) as factive FROM tblLinks INNER JOIN tblHits on tblLinks.id=tblHits.LinkID WHERE tblLinks.ACTIVE='Y' AND tblLinks.CATEGORY='1' ORDER BY HITCOUNT ASC"

    can anyone help me with this sql string?

    do i need to use group by?

    thanks

    T.

  • August 12, 2003 at 10:03 am

    #469600

    What is the FIRST function? Do you mean MIN?

  • August 12, 2003 at 10:09 am

    #469602

    I think what you need is:

    

    SELECT 

      l.id as "fid"

    , l.name as "fname"

    , l.url as "furl"

    , l.description as "fdescription"

    , 'Y' as "factive"

    , COUNT(*) AS "hit_count"

    FROM tblLinks l

      INNER JOIN tblHits h ON l.id = h.id

    WHERE l.active = 'Y'

    AMD   l.Category = '1'

    GROUP BY

      l.id

    , l.name

    , l.url

    , l.description

    , l.active

    ORDER BY COUNT(*) ASC

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply