Results 1 to 5 of 5
  1. #1
    Join Date
    May 2013
    Posts
    5

    Unanswered: Retrieving rows with updates

    Hi All,

    From a table I want to retrieve only the rows that have been updated but I am not able to. Here's the scenario:

    Code:
    select distinct price ,ofr, prd from prod where ofr = '412064'
    gives the result
    price ofr prd
    ------------------------ --------- ---------
    3.76 412064 52884

    likewise when I give
    Code:
    select distinct price ,ofr, prd from prod where ofr = '412061'
    I get
    price ofr prd
    ------------------------ --------- ---------
    6.94 412061 51642
    7.99 412061 51642

    meaning 412064 has no modifications whereas 412061 has been updated twice.

    I want my query to return only the rows which have multiple updates. Please can you help?

    Thanks in advance

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    So for the following data, what should the output be?
    Code:
    price           ofr       prd
    -------------- --------- ---------
         1.23    123456     51642
         6.94    412061     51642
         6.94    412061     51642
         7.99    412061     51642
         7.99    412061     51642
         7.99    412061     51642
         7.99    654321     51642
         7.99    654321     51642
         7.99    777777     51642
         8.99    777777     51642
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    Quote Originally Posted by swasid View Post
    I want my query to return only the rows which have multiple updates.
    Here is an attempt at what I think you are asking about.


    I used Pat's data (hope you don't mind!) and a temporary table to try out a few things.

    Code:
    declare @t1 table
    (
        price real
       ,ofr int
       ,prd int
    )
    
    insert @t1 (price, ofr, prd) values
          (3.76,    412064,     52884)
         ,(6.94,    412061,     51642)
         ,(7.99,    412061,     51642)
         ,(1.23,    123456,     51642)
         ,(6.94,    412061,     51642)
         ,(6.94,    412061,     51642)
         ,(7.99,    412061,     51642)
         ,(7.99,    412061,     51642)
         ,(7.99,    412061,     51642)
         ,(7.99,    654321,     51642)
         ,(7.99,    654321,     51642)
         ,(7.99,    777777,     51642)
         ,(8.99,    777777,     51642)
    I am guessing that price is relevant. But there there is nothing in the data to tell the cte which of the "multiple updates" should be selected. I have no idea what ofr is or represents and I am guessing, again, that prd is a product number.

    With that said, the following code uses a simple cte to determine if there are multiple rows.

    Code:
    ;with ofr_cte as
    (
       select price, ofr, prd,
          ROW_NUMBER() over (partition by ofr order by ofr) rowNum
          from @t1
    )
    
    select price, ofr, prd
      from ofr_cte 
      where ofr_cte.rowNum = 2
    Which produces:

    Code:
    price	ofr	prd
    6.94	412061	51642
    7.99	654321	51642
    8.99	777777	51642

  4. #4
    Join Date
    May 2013
    Posts
    5
    Hi PatP and LinksUP,

    Many thanks for your replies. Just to clarify, ofr indicates offer and prd is the product attached to that offer. There could be many products attached to a single offer( buy 1 get 1) but not vice versa. Below is the slightly modified Pat's data

    Code:
    price           ofr       prd
    -------------- --------- ---------
         1.23    123456     61642
         6.94    412061     51642
         6.94    412061     51642
         7.99    412061     51642
         7.99    412061     51642
         7.99    412061     51642
         7.99    654321     71642
         7.99    123456     71642
         7.99    777777     81642
         8.99    777777     91642
    So the result should like this(considering an order by on ofr)
    Code:
       price         ofr       prd
    -------------- --------- ---------
         1.23     123456     61642
         7.99     123456     71642  
         6.94     412061     51642
         7.99     412061     51642
         7.99     777777     81642
         8.99     777777     91642

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Code:
    SELECT DISTINCT price, ofr, prd
    FROM TheTable a
    WHERE EXISTS 
    (SELECT 1 FROM TheTable b WHERE a.ofr=b.ofr GROUP BY ofr HAVING count(*)>1)

Posting Permissions

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