locked
SELECT Top 1 RRS feed

  • Question

  • I have a Table called bids and a table called AuctionRoll

    For Each product in the AuctionRoll there are many bids,

    How would I be able to Select just the highest bid from the for the product in the AuctionRoll?


    Regards Ismail

    Monday, July 23, 2012 6:12 AM

Answers

  • there are mainly 2 ways can do such:(Group by is not included here as you might end up grouping many columns for your needs)

    1. Use the row_Number()

    2, Use the Cross Apply.

    Base on the number of your Products in AuctionRoll and your CPU resources you have, use Cross Apply option might take the advantage of Parallelism and therefore give you better option.

    However, if you have many products then use row_Number will be a better option. I will five you both option as below:

    1. Use row_Number:

    ;With CTE
    As
    (
    Select A.ProductID, B.*
    		, ROW_NUMBER() Over(Partition By A.ProductID Order by Bids_Date_Time Desc) As RN 
    		--My order by column here might not be correct to you, you need to change by your definition which difines the order
    From	AuctionRoll A
    			Inner Join
    		Bids B
    			A.ProductID = B.ProductID
    )
    Select	*
    From	CTE
    Where RN = 1	


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!


    Monday, July 23, 2012 6:26 AM
  • 2. Use Cross Apply:

    Select A.ProductID, C.*
    From	AuctionRoll A
    			Cross Apply
    		(
    			Select TOP 1 B.*
    			From	Bids B
    			Where	B.ProductID = A.ProductID
    			Order by Order by B.Bids_Date_Time Desc
    		) C


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    • Proposed as answer by Naomi N Monday, July 23, 2012 4:02 PM
    • Marked as answer by amber zhang Monday, July 30, 2012 5:52 AM
    Monday, July 23, 2012 6:29 AM

All replies

  • Try

    select product,max(bid)

    from AuctionRoll

    group by product;


    Many Thanks & Best Regards, Hua Min

    Monday, July 23, 2012 6:16 AM
  • Could you please post the table structures and sample data?

    Here is my guess and you are looking for something like below.

    DECLARE @AuctionRoll TABLE
    (
        autoid int identity(1,1),
        product_name varchar(100)
    )

    insert into @AuctionRoll
    SELECT 'Product 1' UNION ALL
    SELECT 'Product 2' UNION ALL
    SELECT 'Product 3'

    DECLARE @bids  TABLE
    (
        bid_id int identity(1,1),
        autoid int,
        bid_amount float
    )

    INSERT INTO @bids(autoid,bid_amount)
    SELECT 1,10.25 UNION ALL
    SELECT 1,11.25 UNION ALL
    SELECT 2,10.25 UNION ALL
    SELECT 2,13.25 UNION ALL
    SELECT 3,10.25 UNION ALL
    SELECT 3,12.25
     
    SELECT BIDS.autoid,Auction.product_name,MAX(bid_amount) FROM @bids BIDS
    JOIN @AuctionRoll Auction ON Auction.autoid = BIDS.autoid
    GROUP BY  BIDS.autoid,Auction.product_name

     

    Best Regards, Venkat

    Monday, July 23, 2012 6:24 AM
  • there are mainly 2 ways can do such:(Group by is not included here as you might end up grouping many columns for your needs)

    1. Use the row_Number()

    2, Use the Cross Apply.

    Base on the number of your Products in AuctionRoll and your CPU resources you have, use Cross Apply option might take the advantage of Parallelism and therefore give you better option.

    However, if you have many products then use row_Number will be a better option. I will five you both option as below:

    1. Use row_Number:

    ;With CTE
    As
    (
    Select A.ProductID, B.*
    		, ROW_NUMBER() Over(Partition By A.ProductID Order by Bids_Date_Time Desc) As RN 
    		--My order by column here might not be correct to you, you need to change by your definition which difines the order
    From	AuctionRoll A
    			Inner Join
    		Bids B
    			A.ProductID = B.ProductID
    )
    Select	*
    From	CTE
    Where RN = 1	


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!


    Monday, July 23, 2012 6:26 AM
  • Try the below:

    Create Table AuctionRoll(ProductID int)
    Create Table bid(ProductID int, bidvalue int)
    Insert into AuctionRoll Select 100
    Insert into AuctionRoll Select 101
    Insert into bid Select 100,1000
    Insert into bid Select 100,2000
    Insert into bid Select 101,20000
    Select A.ProductID, MAX(B.BidValue) From
    AuctionRoll A
    Inner Join bid B on A.ProductID = B.Productid
    --If you have a particular productid to be searched.
    --Where A.ProductID =100
    Group by A.ProductID



    • Edited by SQLZealots Monday, July 23, 2012 6:32 AM
    Monday, July 23, 2012 6:27 AM
  • 2. Use Cross Apply:

    Select A.ProductID, C.*
    From	AuctionRoll A
    			Cross Apply
    		(
    			Select TOP 1 B.*
    			From	Bids B
    			Where	B.ProductID = A.ProductID
    			Order by Order by B.Bids_Date_Time Desc
    		) C


    View Steven Wang's profile on LinkedIn | Blog: MSBICOE.com | MCITP - BI, SQL Developer & DBA

    Hate to mislead others, if I'm wrong slap me. Thanks!

    • Proposed as answer by Naomi N Monday, July 23, 2012 4:02 PM
    • Marked as answer by amber zhang Monday, July 30, 2012 5:52 AM
    Monday, July 23, 2012 6:29 AM