Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2012
    Posts
    1

    Unanswered: help in writing a query

    i have a table of items which have 3 attributes (item_name ,username, bid_price).
    So, in this table for each item there are many 'bid_price' (same and different) possible by different users.
    So, I want to create a view of item_name, username, bid_price and condition on bid_price is that it should be unique and smallest for a particular item.

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    So, if there are two identical bids for a given item, you do not wish to see this bid? Is there no way to further differentiate the bids (i.e. first bid?) You would need a time stamp or serial field to determine this, though.

    What version of pg are you using?
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Try this one:
    Code:
    create or replace view v_bids
    as
    select item_name,
           username,
           bid_price as lowest_bid_price
    from (
       select item_name, 
              username, 
              bid_price,
              row_number() over (partition by item_name order by bid_price asc) as rn
       from items
    ) t
    where rn = 1

Tags for this Thread

Posting Permissions

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