Results 1 to 15 of 15

Thread: How to Select ?

  1. #1
    Join Date
    May 2011
    Posts
    7

    Unanswered: How to Select ?

    Dear All,

    How to Select the first row from the table where
    psp_item_no = DRR07DFAB0034
    psp_ps_no = 16

    ?
    Attached Thumbnails Attached Thumbnails Com.JPG  

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Define "First".
    There appears to be no way to order the records in your data set.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    Define "First".
    There appears to be no way to order the records in your data set.
    in that case, TOP 1 without an ORDER BY clause makes sense!!

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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You know that won't guarantee identical results each execution, right?
    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
    Quote Originally Posted by blindman View Post
    You know that won't guarantee identical results each execution, right?
    who, me? yes, i know

    but it meets all the requirements of the original problem
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Arguably, no.
    The requirement was for "first row", where the solution returns "a row".
    If it's not practically useful, then it's practically useless.

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

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman View Post
    Arguably, no.
    The requirement was for "first row", where the solution returns "a row".
    If any row can potentially be the first at any given moment in time, then I think that the requirements have been met.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Great!
    Does that mean I can cut in front of you in the lunch line, since apparently it does not matter to you which of us if first, so long as one of us is first?
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by blindman View Post
    Great!
    Does that mean I can cut in front of you in the lunch line, since apparently it does not matter to you which of us if first, so long as one of us is first?
    You always do, why should this be different?

    Logically, tables have no order (for either rows or columns). Of course there is a physical order, but that is left as an "implementation detail" and it has nothing to do with the logic behind the beast.

    Of course I should go first, because of my innate supriority. Unfortunately every time the lunch bell rings you're off to the races, so I end up behind you!

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Pat Phelan View Post
    Of course I should go first, because of my innate supriority.
    Quoted for posterity.
    If it's not practically useful, then it's practically useless.

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

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    TOP 1 adequately solves "Select the first row from the table"

    no information was given to determine which row should be considered "first" and therefore no ORDER BY is really needed, any row will do
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  12. #12
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Uh, yeah.....but for some reason (decades of experience with this, perhaps), I suspect that the OP actually was referring to the first row according to the order he inserted the records.
    If it's not practically useful, then it's practically useless.

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

  13. #13
    Join Date
    May 2011
    Posts
    7
    update #temp
    set #temp.Quantity_mc = a.Total
    from #temp Inner join
    (
    selectpsp_item_no,psp_item_var,sum(psp_qty_prpnl) as Total
    from pmddb..pmd_mpsp_ps_postn pmd
    WHERE NOT EXISTS (SELECT[/color] * From common..ims_variant_master ims,pmddb..pmd_mpsp_ps_postn pmd2
    where description like 'Optional%' and
    ims.stock_no= pmd2.psp_item_no and
    pmd2.psp_ps_no = pmd.psp_ps_no and
    pmd2.psp_io_flag = 'o'
    )

    group by psp_item_no,psp_item_var
    ) as a
    on #temp.item_code = a.psp_item_no AND
    #temp.variant_code=a.psp_item_var

    This is my code where I want update my Temp Table.

    Temp Table Definition :

    create table #temp
    (
    item_code varchar(50) ,
    variant_code varchar(10),
    item_desc varchar(200),
    uom varchar(10),
    Quantity_mc numeric(28,3),
    Available_Quantity numeric(28,3),
    no_of_mechine numeric(28,3),
    reminder numeric(28,3),
    bom_item_code varchar(50),
    bom_variant_code varchar(10),
    bom_item_desc varchar(200),
    bom_uom varchar(10),
    )


    The set of Result I have received by the code

    group by psp_item_no,psp_item_var


    is :
    Code:
    psp_ps_no       psp_item_no     psp_item_var    psp_io_flag
    7                 DRR07DFAB0034        ##                 I
    15               DRR07DFAB0034        ##                I
    But I only want the psp_ps_no =7 row, because this row has only relation with the Input psp_ps_no =16.

    The relation I've to show in the Excel Sheet herewith :
    Attached Thumbnails Attached Thumbnails untitled.JPG  

  14. #14
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by blindman View Post
    ... I suspect that the OP actually was referring to the first row according to the order he inserted the records.
    fine, be that way

    go ahead and help him update his #temp table now, okay?

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

  15. #15
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    subrati,

    The code you provided is littered with syntax errors. There is no way this is the code that you are using.
    Additionally, your original post was regarding an ordered SELECT, while your code appears to perform an UPDATE, and has nothing to do with the requirements you've stated previously.
    If it's not practically useful, then it's practically useless.

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

Posting Permissions

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