Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2004
    Posts
    1

    Question Unanswered: Select First Occurance of a row

    Hey all:

    I have a table that contains the following records:
    ptrecid paidby amt chequeno name
    4791 A X A 1200 097760 LOWE, Bernard
    4791 A X A 4380 097760 LOWE, Bernard
    4791 A X A 620 106406 LOWE, Bernard

    I need to create a view that returns only the FIRST occurance of each distinct ptrecid. I do NOT want to sum amt/chequeno, but rather return only the values in the first record. Using a group by gets me close, but since the amt and chequeno are not unique, they do not group. Min also does not work, as 620 is returned for amt, and 097760 is returned for chequeno.

    The desired result from this query would return:
    4791 A X A 1200 097760 LOWE, Bernard

    Any help on this is greatly appreciated.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    There is no such thing as the "First Row" of an unordered dataset.

    If, for instance, you have in incrementing value that is either unique in itself or part of the natural key (such as a surrogate key or datetime value) then you can use this subquery method

    select yourtable.*
    from yourtable
    inner join
    (select ptrecid, min(keyvalue) as keyvalue from yourtable group by ptrecid) firstrecords
    on yourtable.ptrecid = firstrecords.ptrecid
    and yourtable.keyvalue = firstrecords.keyvalue
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Feb 2002
    Location
    Assam, India
    Posts
    55

    Smile

    --TRY THIS PROCEDURE-
    --SQL CODE BEGINS HERE

    -- exec print_first_row_prectid
    create proc print_first_row_prectid
    as
    begin
    declare @ptrectid_prev as varchar(50)
    declare @ptrectid_next as varchar(50)
    declare @paidby as varchar(50)
    declare @amt as float
    declare @chequeno as varchar(50)
    declare @name as varchar(50)

    create table #temp1 (
    ptrectid varchar(50),
    paidby varchar(50),
    amt float,
    chequeno varchar(50),
    check_name varchar(50)

    )

    set @ptrectid_prev = 'hello'

    DECLARE Check_cursor CURSOR FOR

    SELECT ptrecid, paidby, amt, chequeno, name
    from test1
    OPEN Check_cursor
    FETCH NEXT FROM Check_cursor into @ptrectid_next, @paidby, @amt, @chequeno, @name
    -- Check @@FETCH_STATUS to see if there are any more rows to fetch.
    WHILE @@FETCH_STATUS = 0
    BEGIN
    if @ptrectid_prev <> @ptrectid_next
    begin
    insert into #temp1 (ptrectid, paidby, amt, chequeno, check_name)
    select @ptrectid_next, @paidby, @amt, @chequeno, @name

    end
    set @ptrectid_prev = @ptrectid_next

    FETCH NEXT FROM Check_cursor into @ptrectid_next, @paidby, @amt, @chequeno, @name
    END
    CLOSE Check_cursor
    DEALLOCATE Check_cursor

    select * from #temp1

    end


    -- SQL CODE ENDS HERE

    Roshmi Choudhury

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Yes, there is no SQL problem so common and simple that it cannot be made more complex and bloated by using a cursor.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when all you have is a hammer, every problem looks like a nail

    your restraint, blindman, is admirable

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    In a table, there is no "order" for rows or columns... Logically the whole mess is just a pile of data to SQL. When you create a result set from the data (usually by executing a SELECT statement), you can put the columns and rows into order (using a column list and an ORDER BY clause) or take "pot luck" however the database chooses to serve them up.

    Can you explain what you consider to be the "order" of the rows so that we can understand what you mean by the "first" row?

    -PatP

  7. #7
    Join Date
    Feb 2002
    Location
    Assam, India
    Posts
    55
    Pat,
    Yes, you are right. I was just about to tell, the SELECT statement for the cursor will need the ORDER BY clause, order by precid.

    I hope if there are many rows that have the same precid, the one that is entered first, will show up first. I am not sure about it though. Please can any one comment.

    Roshmi Choudhury

Posting Permissions

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