SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast jR≥'s Avatar
    Join Date
    Sep 2003
    Location
    belgium
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Newest items, query logic

    Hi
    I want a query that does the following thing,
    Select the 10 latest items added in my database, but only the latest item of each category.
    databaselayout:
    Category
    id, name
    Item
    id, name, dateadded, categoryid

    Query should be something like :
    select * from Item
    group by categoryid
    having count(categoryid)=1
    order by dateadded desc
    limit 0,10

    but I get 0 rows returned.. What's wrong with my query logic?
    thanks in advance!

  2. #2
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    ÷rebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    Try removing the HAVING clause. Any change?

    Yours, Erik.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Code:
    select * 
      from Item as o
     where dateadded
         = ( select max(dateadded)
               from Item
              where categoryid = o.categoryid )
    limit 10
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast jR≥'s Avatar
    Join Date
    Sep 2003
    Location
    belgium
    Posts
    29
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    Code:
    select * 
      from Item as o
     where dateadded
         = ( select max(dateadded)
               from Item
              where categoryid = o.categoryid )
    limit 10
    Ok thanks, it worked out well,
    that good 'old max() always forgetting that one
    final query looks like this.. quite slow one :s
    (working with more than 1 category for an item )
    Code:
    "select ic.categoryid,i.itemid,i.name,i.urlname,location,dateadded 
    from items i
    	inner join itemcategory ic on i.itemid=ic.itemid
    	inner join categories c on ic.categoryid=c.id		
    	where categorytype=$categoryType
    	and dateadded =( select max(dateadded)
               			from items i2
               			inner join itemcategory ic2 on i2.itemid=ic2.itemid
               		        where ic2.categoryid=ic.categoryid )
    order by dateadded desc
    limit 0,$offset"
    Well I wonder if this will work for mysql < 4.1..
    only tested it out on 4.1.. but my production server is 4.0
    let's hope there's no need to rewrite it as a join

  5. #5
    Tranceoholic lilleman's Avatar
    Join Date
    Feb 2004
    Location
    ÷rebro, Sweden
    Posts
    2,716
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi,

    The query will not work on versions lower than 4.1, since it's using a subselect.

    Yours, Erik.
    ERIK RIKLUND :: Yes, I've been gone quite a while.

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,016
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    i don't think you need that join in the subquery

    you want the latest item in each category

    the item table has a column called categoryid

    that's sufficient
    r937.com | rudy.ca | Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •