Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2009
    Posts
    4

    Question Unanswered: first row from a table...

    Hi...I am new at this...I just have this new issue at work and thinking how to get this issue resolved....i have got a table B linked to another table A, where I want the 1st row of table B to be returned only...
    i have got the following query...

    Code:
    SELECT     A.SERIES, A.ORMSTRNM, A.JRNENTRY, A.TRXDATE, A.ORDOCNUM, A.ACTINDX, 
    
                          GL00100.ACTNUMBR_1, GL00100.ACTNUMBR_2, GL00100.ACTNUMBR_3, A.DEBITAMT, A.CRDTAMNT, A.DSCRIPTN, 
    
                          A.REFRENCE, A.ORMSTRID, RM00101.STMTNAME, POP30310.PONUMBER, POP10100.POPCONTNUM, B.PACONTNUMBER, 
    
                          B.LNITMSEQ
    
    FROM         A INNER JOIN
    
                          GL00100 ON A.ACTINDX = GL00100.ACTINDX LEFT OUTER JOIN
    
                          B ON A.ORDOCNUM = B.PADocnumber20 LEFT OUTER JOIN
    
                          POP30310 INNER JOIN
    
                          POP10100 ON POP30310.PONUMBER = POP10100.PONUMBER ON A.SEQNUMBR = POP30310.RCPTLNNM AND 
    
                          A.ORDOCNUM = POP30310.POPRCTNM LEFT OUTER JOIN
    
                          RM00101 ON A.ORMSTRID = RM00101.CUSTNMBR
    
    WHERE     (GL00100.ACTNUMBR_3 = '41011') AND (A.TRXDATE BETWEEN CONVERT(DATETIME, '2009-07-01 00:00:00', 102) AND CONVERT(DATETIME, 
    
                          '2009-07-31 00:00:00', 102)) AND (A.ORDOCNUM = 'PRINV010871'))
    All I want is to get the first row to be considered from the table 'B'...When the link between Table A and Table B is done, I want it to work with only the first row of Table 'B', not the rest of Table 'B' rows...what can I do to achive this....need help on this at the earliest....Thanks....

    note: B.PACONTNUMBER is the field from table B, for which I want the first row to be considered....

    --------------

    Sayed Faiz

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    How do you define the "first" record?
    George
    Home | Blog

  3. #3
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    database the order meaning no of data in the has
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Thrasymachus
    database the order meaning no of data in the has
    Quote of the month.
    George
    Home | Blog

  5. #5
    Join Date
    Oct 2003
    Posts
    60

    Talking

    Quote Originally Posted by Thrasymachus
    database the order meaning no of data in the has

    UMmmmmmmmmmmmmmmmmmm....

    no?

    LOL


    Quote Originally Posted by gvee
    Quote of the month.
    YEP!

  6. #6
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    enough jokes. in relational database theory, the order of the data has no meaning. george's question still stands. This can be done, but we need to still define what you define as the "first".
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  7. #7
    Join Date
    Aug 2009
    Posts
    4

    Question

    i need the first record of that field which has the lowest value....

  8. #8
    Join Date
    Aug 2009
    Posts
    4

    Question

    can anyone still provide me some clue on how cn i do this???..if you show me the function in sql for doing this...and how the function needs to be used then that would be useful...i will then apply that to my query....thanks...

  9. #9
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Now you are getting somewhere. You want the Minimum value. The Function is MIN. You can go about it in one of two ways (there may be others):

    Non-correlated sub-query:
    Code:
    SELECT col-list
    FROM table-name
    WHERE col1 = (SELECT MIN(col2)
                  FROM table-name
                 )
    This just finds the Minimum value in a table (could be the same as the outer query or a different one) and use that for the COL1 = comparison.
    Correlated sub-query:
    Code:
    SELECT col-list
    FROM table-name A
    WHERE col1 = (SELECT MIN(col2)
                  FROM table-name B
                  WHERE A.col3 = B.col3
                 )
    This correlates the rows of the outer query with the rows of the sub-query to find the Minimum value for a certain value. For example if COL3 had values of 'A', 'B', and 'C', the sub-query would return the Minimum value for the Col3 = 'A' rows, the Minimum values for the Col3 = 'B' rows and the Minimum value for the Col3 = 'C' rows.

    The Minimum values could be the same or different values. This is different from the non-correlated sub-query which gets only one value.

    The one you use depends on what you want to accomplish.

  10. #10
    Join Date
    Aug 2009
    Posts
    4

    Question

    thanks for the help....what if now I want to use only the first row of Table 'B', without worrying about if it's a minimum value or maximum value....i just want the query to work with the 1st row found in table 'B'....can we use a function like TOP...if so, how can i use the TOP function(maybe use the 'TOP' function in a sub-query)....

  11. #11
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    The top command is:

    SELECT TOP x col-list
    FROM table-name
    WHERE ...

    Where x is a positive integer. In your case 1 which will return the first row that is found. This row is 'random' as there is no guarantee what row will be returned so it can be different with different executions of the query.

    Can you use TOP in a sub-query? I don't know. I have never has a reason to get a random row from a table.

  12. #12
    Join Date
    Dec 2008
    Posts
    135
    go through this link
    Return TOP N rows - Madhivanan

Posting Permissions

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