Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2008
    Posts
    120

    Unanswered: Select TOP 1 and Count

    Hi everyone
    I've searched for a solution to this but so far i've been unable to find the answer.
    I have a Select TOP 1 statement which works fine, but i also want to count the total records at the same time.

    this is my SQL
    Code:
    SELECT     TOP (1) dbo.ClientOffers.ProductID, dbo.ClientOffers.ClientID, dbo.Offers.Offertext, dbo.Offers.Offerlink, dbo.Offers.Image, dbo.Offers.Cost
    FROM         dbo.ClientOffers INNER JOIN
                          dbo.Offers ON dbo.ClientOffers.OfferID = dbo.Offers.OfferID
    WHERE     (dbo.ClientOffers.ProductID = 1284) AND (dbo.ClientOffers.ClientID = 0)
    How do i also count the total records?
    Thanks
    Andy

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    since you use TOP without ORDER BY, there's no way to know which row is the first one, i.e. the query returns an arbitrary row

    therefore, the question doesn't make sense

    my advice is to use two queries
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2008
    Posts
    120
    oops!
    You're quite right r937

    I've added the ID and ordered by it

    Code:
    SELECT     TOP (1) dbo.ClientOffers.ProductID, dbo.ClientOffers.ClientID, dbo.ClientOffers.ID, dbo.Offers.Offertext, dbo.Offers.Offerlink, dbo.Offers.Image, 
                          dbo.Offers.Cost
    FROM         dbo.ClientOffers INNER JOIN
                          dbo.Offers ON dbo.ClientOffers.OfferID = dbo.Offers.OfferID
    WHERE     (dbo.ClientOffers.ProductID = 1284) AND (dbo.ClientOffers.ClientID = 0)
    ORDER BY dbo.ClientOffers.ID
    Is there a way to use COUNT now?

    Andy

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by AndyJay View Post
    Is there a way to use COUNT now?
    perhaps

    could you first take a moment to explain why you want both a single detailed row and a count?

    and note, two queries is still a good solution
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2008
    Posts
    120
    Hi r937

    A product may have more than one (free or chargeable offer).
    If the product has more than one offer, there is an option to change the default offer.
    So my product detail page displays the product.
    If the product has a single offer, no other options are displayed.
    If the product has multiple offers, the default offer is displayed and also the other offer options.

    I need to select the TOP 1 Offer (the default offer) and if there are more than one offer, display the other offers as well.

    From the select statement i can display the other offers IF the COUNT is GREATER THAN 1

    Sounds more complicated when trying to put it in to words :-)

    Thanks
    Andy

  6. #6
    Join Date
    Feb 2008
    Posts
    120
    I've done a separate query instead

    Code:
    SELECT     COUNT(ID) AS Total_Offers
    FROM         dbo.ClientOffers
    WHERE     (ProductID = 1284) AND (ClientID = 0)

  7. #7
    Join Date
    Oct 2009
    Location
    221B Baker St.
    Posts
    486
    Rather than making multiple "trips" to the database (get one, get the count, get the rest), you might consider retrieving all of the rows initially and then presenting as needed?

    I suspect there will not be even hundreds of offers for one product - more like just a few.

  8. #8
    Join Date
    Feb 2008
    Posts
    120
    hi papadi
    That's what i was trying to do initially, but i guess doing it in two queries won't put too much demand on the database.
    Out of interest, is there a way of monitoring just how much demand is being put on the db?

    Thanks
    Andy

Posting Permissions

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