Results 1 to 10 of 10

Thread: Need Query

  1. #1
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Red face Unanswered: Need Query

    Hi all

    I have the result of select as following :

    f1 f2 f3 doc_no doc_date value
    ------------------------------------
    1 1 4 240 1-7-2005 500
    1 1 4 133 20-4-2005 600
    1 1 4 90 1-2-2004 900
    2 3 5 88 1-9-2007 200
    2 3 5 23 3-2-2007 788

    I need select that return the first row of each(f1 f2 f3)
    so the result should be :

    1 1 4 240 1-7-2005 500
    2 3 5 88 1-9-2007 200

    thanx in advance

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    What determines the "first row". The highest doc_no? The earliest doc_date?, The lowest value? More information is needed.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >I need select that return the first row of each(f1 f2 f3)
    Records in an RDBMS are like a collection of colored balls in a basket.
    If there are more than 1 red ball in the basket; which is the "first" red ball?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Being a member since 2004 and still not knowing how to format data in order to make other forum members' life easier, well, it is a shame.

    I'm not in the mood to decipher what you've written, but here's a tip: if "the first record" is the one with the minimum date column value, it would look like this:
    Code:
    SELECT * FROM TEST t
    WHERE t.doc_date = (SELECT MIN(t1.doc_date)
                        FROM TEST t1
                        WHERE t1.f1 = t.f1
                         AND t1.f2 = t.f2
                         AND t1.f3 = t.f3
                       );

  5. #5
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Talking

    Thank u (all)

    the case I have is vey very very complicated, and the dummy example I gave is not present the actual case.
    any way the problem is solved.

    Dear (( Littlefoot )) :
    Even the example I gave seems to be silly, I think that you reply was aggressive and you have to be more high-minded, If you find my question is silly you don't have to answer it,
    and I think there is no ((((shame)))) if you ask any think for learning,
    althougth that I review the forum from time to other and I found your answers is very smart.
    sorry for posting the (low-level) question

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Please, do not interpret my words the way they are not written.

    An example is exactly what it is: an example. It is supposed to be simple enough because - who is going to spend hours just to figure out what you wanted to say? As far as I'm concerned, there's nothing "silly" in your message; your sample data is "good". Not "fine" as
    • you didn't FORMAT it correctly! For heaven's sake, is it that difficult to include CODE tags? No, it is not; you've just been lazy, hoping that other forum members wouldn't complain much. Well, I do complain and I believe I'm right about it. I expected this:
      Code:
      f1 f2 f3 doc_no  doc_date value
      -- -- -- ------ --------- -----
       1  1  4    240  1-7-2005   500
       1  1  4    133 20-4-2005   600
      instead of this

      f1 f2 f3 doc_no doc_date value
      ------------------------------------
      1 1 4 240 1-7-2005 500
      1 1 4 133 20-4-2005 600
      ...
    • It would be really nice if you provided a sample test case. It would contain a script which would enable other people to create and, actually, test the solution before posting it (yes, some people really do that):
      Code:
      CREATE TABLE test (f1 NUMBER, f2 NUMBER, ...);
      INSERT INTO test (f1, f2, ...) VALUES (1, 1, ...);
      INSERT INTO test (f1, f2, ...) VALUES (2, 3, ...);
      etc.
      This, along with FORMATTED output you'd like to have is what I call a "properly asked question"
    And yes, it is a shame you didn't learn that for all those years.

    Please, don't think that I'm angry with you; I'm not, I'd just want this forum to be a cozy place to visit and stay. Writing nicely formatted questions and nice + correct answers would help.

    Finally, I'm glad if you managed to solve your very, very, very complicated situation. If my post helped a little bit, fine. If not, oh well, there will be more useless answers in the future, so ... I believe I'll have to learn how to live with it.

  7. #7
    Join Date
    Dec 2007
    Posts
    9

    First posting of Rino11

    Here is my first post to this forum - I'm a pl/sql developer so you are in luck. Use the partition function oracle offers. It is a strong function. Here is an example, if the info that you provided was in a table in the same order and you needed only those rows to show:
    SELECT *
    FROM
    (SELECT F1,F2,F3,DOC_NO,DOC_DATE,ROWNUM nu,
    first_value(ROWNUM) over (PARTITION BY f1||f2||f3 ORDER BY f1||f2||f3 DESC) pt
    FROM DB_TEST)
    WHERE pt = nu

    Rino11 - Remember, where ever you go, there you are!

  8. #8
    Join Date
    Feb 2004
    Location
    Jordan
    Posts
    137

    Talking

    Thank you alooooooooooooot Rino11

    you answer is so smart, and the partition function is very helpful, I got the same answer from another forum :

    http://forums.oracle.com/forums/thre...30227&#2230227

    we have something in common (11)

    =============

    to littlefoot :
    I hope (partition function ) helps you

  9. #9
    Join Date
    Dec 2007
    Posts
    9
    No Prob, and glad to help -

    Rino11
    Last edited by Rino11; 12-13-07 at 23:23.

  10. #10
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    It is not a "partition" function; its name is "analytical" function.

Posting Permissions

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