Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2004
    Posts
    39

    Unanswered: SQL SELECT Question

    I have this SELECT:

    select fond, dataextr, codbanca from extrbanc
    group by fond, dataextr, codbanca
    order by fond, codbanca, dataextr desc

    that returns something like:
    1 2005-01-10 00:00:00 3
    1 2005-01-05 00:00:00 3
    1 2005-01-03 00:00:00 3
    1 2005-01-10 00:00:00 6
    1 2005-01-07 00:00:00 6
    1 2005-01-06 00:00:00 6
    1 2005-01-05 00:00:00 6
    1 2005-01-04 00:00:00 6
    1 2005-01-03 00:00:00 6
    1 2005-01-01 00:00:00 81
    1 2005-01-10 00:00:00 82
    1 2005-01-07 00:00:00 82
    1 2005-01-06 00:00:00 82
    1 2005-01-05 00:00:00 82
    1 2005-01-04 00:00:00 82
    1 2005-01-03 00:00:00 82


    What I need is to return only the 1st appearance for each codbanca. This means something like:
    1 2005-01-10 00:00:00 3
    1 2005-01-10 00:00:00 6
    1 2005-01-01 00:00:00 81
    1 2005-01-10 00:00:00 82

    Do you have any idea how should I modify my SELECT?

  2. #2
    Join Date
    Feb 2004
    Posts
    88
    Hi,

    you need to define what you mean by "the first appearance by each codbanca".

    If you mean the row with the earliest dataextr, per fond/codbanca combination, then the followng will do the job:

    select fond, min(dataextr), codbanca
    from extrbanc
    group by fond, codbanca
    order by fond, codbanca

    if you mean the row with the earliest dataextr per codbanca, then it gets a little more complex:

    select a.fond, b.min_dataextr, a.codbanca
    from extrbanc a,
    (select codbanca, min(dataextr) as min_dataextr
    from extrbanc
    group by codbanca) as b
    where a.codbanca = b.codbanca

    might do the trick.

    HTH,

    Bill
    select

  3. #3
    Join Date
    Nov 2004
    Posts
    39
    yeap, it's fine
    Actually, I've used a max(dataextr) exactlly as you said.
    thx a lot

Posting Permissions

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