Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2015
    Posts
    5

    Question Unanswered: Show all values with same number and date before ...

    Hi,

    Ive a Table like:

    Number Value Date
    1 443 20/11/2001 14:49:36
    2 443 20/11/2001 12:07:46
    3 331 08/10/2004 09:53:25
    4 331 06/10/2004 09:23:11
    5 331 08/10/2004 19:00:00

    How do I write a SQL to Show only the values with lower date

    Result should be:

    Number Value Date
    2 443 20/11/2001 12:07:46
    3 331 08/10/2004 09:53:25
    4 331 06/10/2004 09:23:11


    thanks in advance
    Kind regards
    Dior

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    This should work, but replace the column and table names with the real column and table names. The names VALUE,DATE, and NUMBER are reserved words and can't be used.

    Code:
    SELECT X.number, X.value, X.date
      FROM (SELECT b.number
                   DENSE_RANK ()
                   OVER (PARTITION BY B.value
                         ORDER BY B.date ASC)
                      Dr,
                   B.VALUE,
                   B.NUMBER
             FROM MY_TABLE) X
    WHERE X.Dr = 1;
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Oct 2015
    Posts
    5
    Hi,

    this works great, Thank you!

    One more addition to the question:

    Ive in my table sometimes the case that 2 entries exact the same. The result is they have both the Rank Numer 3.

    How to change the SQL to get 1 and 2 for the two entries?

  4. #4
    Join Date
    Oct 2015
    Posts
    5

    3456

    Hi,

    this works great, Thank you!

    One more addition to the question:

    Ive in my table sometimes the case that 2 entries exact the same. The result is they have both the Rank Numer 3.

    How to change the SQL to get 1 and 2 for the two entries?

  5. #5
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I am assuming that the number is different but the value and the date is the same. Then

    Code:
    select number,value,date
    from
    (
    SELECT min(b.number) number,
                   RANK ()
                   OVER (PARTITION BY B.value
                         ORDER BY B.date ASC)
                      Dr,
                   B.value,
                   B.date
             FROM whb b
             group by b.value,b.date)
    where dr=1
    Last edited by beilstwh; 10-16-15 at 17:04.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Quote Originally Posted by Dior
    this works great
    No, it does not (according to what you described as a desired output), not to mention syntax errors. Perhaps you should first run a query and then judge whether it is correct or not.

    I'm not saying that Bill did something wrong - he just didn't test it either, I presume; otherwise, he'd fix it already.

    Anyway: none of Bill's queries returns correct results. The first one misses one record, while the second one misses two of them (if we add a record having "number = 6" in the following case, which represents the "duplicate").

    Here you go: First, an oldfashioned query which solves both of your questions (at least, the way I understood them). For the first question, comment line #5.
    Code:
    SQL> with test as (
      2    select 1 rn, 443 val, to_date('20.11.2001 14:49:36', 'dd.mm.yyyy hh24:mi:ss') dat from dual union
      3    select 2 rn, 443 val, to_date('20.11.2001 12:07:46', 'dd.mm.yyyy hh24:mi:ss') dat from dual union
      4    select 3 rn, 331 val, to_date('08.10.2004 09:53:25', 'dd.mm.yyyy hh24:mi:ss') dat from dual union
      5    select 6 rn, 331 val, to_date('08.10.2004 09:53:25', 'dd.mm.yyyy hh24:mi:ss') dat from dual union
      6    select 4 rn, 331 val, to_date('06.10.2004 09:23:11', 'dd.mm.yyyy hh24:mi:ss') dat from dual union
      7    select 5 rn, 331 val, to_date('08.10.2004 19:00:00', 'dd.mm.yyyy hh24:mi:ss') dat from dual
      8  )
      9  select t.rn, t.val, t.dat
     10  from test t
     11  where t.dat = (select min(t1.dat) From test t1
     12                 where t1.val = t.val
     13                   and trunc(t1.dat) = trunc(t.dat)
     14                );
    
            RN        VAL DAT
    ---------- ---------- -------------------
             2        443 20.11.2001 12:07:46
             3        331 08.10.2004 09:53:25
             4        331 06.10.2004 09:23:11
             6        331 08.10.2004 09:53:25
    
    SQL>
    Secondly, fixed Bill's query for your first question (which, in turn, also solves both problems; don't forget to comment line #5 for the first test case, without the duplicate):
    Code:
    SQL> with my_table as (
      2    select 1 rn, 443 val, to_date('20.11.2001 14:49:36', 'dd.mm.yyyy hh24:mi:ss') dat from dual union
      3    select 2 rn, 443 val, to_date('20.11.2001 12:07:46', 'dd.mm.yyyy hh24:mi:ss') dat from dual union
      4    select 3 rn, 331 val, to_date('08.10.2004 09:53:25', 'dd.mm.yyyy hh24:mi:ss') dat from dual union
      5    select 6 rn, 331 val, to_date('08.10.2004 09:53:25', 'dd.mm.yyyy hh24:mi:ss') dat from dual union
      6    select 4 rn, 331 val, to_date('06.10.2004 09:23:11', 'dd.mm.yyyy hh24:mi:ss') dat from dual union
      7    select 5 rn, 331 val, to_date('08.10.2004 19:00:00', 'dd.mm.yyyy hh24:mi:ss') dat from dual
      8  )
      9  SELECT X.rn, X.val, X.dat
     10    FROM (SELECT b.rn,
     11                 DENSE_RANK ()
     12                 OVER (PARTITION BY B.val, trunc(b.dat)
     13                       ORDER BY B.dat ASC)
     14                    Dr,
     15                 B.val,
     16                 B.dat
     17           FROM MY_TABLE b) X
     18  WHERE X.Dr = 1;
    
            RN        VAL DAT
    ---------- ---------- -------------------
             4        331 06.10.2004 09:23:11
             3        331 08.10.2004 09:53:25
             6        331 08.10.2004 09:53:25
             2        443 20.11.2001 12:07:46
    
    SQL>

  7. #7
    Join Date
    Oct 2015
    Posts
    5
    Not getting the correct results for my table (more than 5 million entries).
    And I have a additional question:

    Code:
    Inumber       dval1 	                dval2	      dat1	          dat2	              prize
    612591456	  250503,1 	70109,86	      	           21.11.2014 08:23	938.855
    612591455	  250503,1 	70109,86	     	           21.11.2014 08:23	938.812
    612591454	  250503,1 	70109,86	     	           21.11.2014 08:23	938.812
    612591457	  250503,1 	70109,86	     	           21.11.2014 08:23	?
    427449917	  253669,05	73124,28	   05.11.2014 	   05.11.2014 09:00	494.69
    427449906	  253669,05	73124,28	   05.11.2014	   05.11.2014 09:00	0.00
    426753875	  253940,09	59067,48	   26.03.2014	   27.03.2014 09:35	554.67
    426753847	  253940,09	59067,48	   26.03.2014	   27.03.2014 09:35	554.69
    Now I only need to write a delete script where the only rows be left are:

    Code:
    612591455	250503,1 	70109,86		                            21.11.2014 08:23	938.812
    427449917	253669,05	73124,28 	05.11.2014	05.11.2014 09:00	494.69
    426753875	253940,09	59067,48	              26.03.2014	27.03.2014 09:35	554.67
    so Ive to delete the following entries from the table:
    Code:
    Inumber	              dval1	              dval2	     dat1	              dat2	                 prize
    612591456	250503,1	              70109,86		              21.11.2014 08:23	   938.855
    612591454	250503,1     	70109,86		              21.11.2014 08:23	   938.812
    612591457	250503,1 	70109,86		              21.11.2014 08:23	   ?
    427449906	253669,05	73124,28	    05.11.2014	05.11.2014 09:00	   0.00
    426753847	253940,09	59067,48	    26.03.2014	27.03.2014 09:35	   554.69
    The criteria are in order of priority (dval1 and dval2 has to be the same):
    1. if there is a prize take the lower one ("?, 0.00, null are no value")
    2. if dat1 exist take the newer one if not exist take dat2 for comparison
    3. if dat1 and or dat2 and prize are equal take only one (maybe the one with the highest Inumber)

    tried to adapt the examples you gave me with the criterias but get not the correct results.

    thx for your help!!
    Last edited by Dior; 10-20-15 at 09:38.

  8. #8
    Join Date
    Oct 2015
    Posts
    5
    ..........

Posting Permissions

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