Select max 1 record per grouping 1

[ratjetoes]

hi,

i need to create a query which only returns one record per grouping.

for example:

TABLE_X
FIELD_A
FIELD_B
FIEL_DATE_TIME

Something like this:
SELECT FIELD_A, FIELD_B, MAX(FIELD_DATE_TIME)
FROM TABLE_X
GROUP BY FIELD_A, FIELD_B

Thing is, i need the MAX(FIELD_DATE_TIME) being calculated per FIELD_B without FIELD_A. Still i need FIELD_A in the select clause for furher joining.

Basically i need this:
Per FIELD_B value i need to return max 1 record with values FIELD_A, FIELD_B and FIELD_DATE_TIME = MAX(FIELD_DATE_TIME).

Anyone any ideas?

t.i.a.,
ratjetoes.

 

[SantaMufasa]

Per FIELD_B value i need to return max 1 record with values FIELD_A, FIELD_B and FIELD_DATE_TIME = MAX(FIELD_DATE_TIME).

This specification is a bit sketchy without either additional explanation or without some sample data and an example of what you want.


Therefore, I'll make an assertion, which you can then adjust to fit your need:

select * from table_x;

FIELD_A    FIELD_B FIELD_DAT
------- ---------- ---------
      1          1 29-OCT-08
      1          2 30-OCT-08
      1          3 31-OCT-08
      2          1 01-NOV-08
      2          2 02-NOV-08
      2          3 03-NOV-08
      3          1 04-NOV-08
      3          2 05-NOV-08
      3          3 06-NOV-08
      2          1 07-NOV-08
      3          3 08-NOV-08
      1          3 09-NOV-08

(And a query that [i]seems[/i] to do what you want)

select field_a, field_b, max_B_date
  from table_x
      ,(select max(field_date_time) max_b_date from table_x)
 group by field_a, field_b,max_b_date;

FIELD_A    FIELD_B MAX_B_DAT
------- ---------- ---------
      1          1 09-NOV-08
      1          2 09-NOV-08
      1          3 09-NOV-08
      2          1 09-NOV-08
      2          2 09-NOV-08
      2          3 09-NOV-08
      3          1 09-NOV-08
      3          2 09-NOV-08
      3          3 09-NOV-08

Let us know if this is, or is not, what you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ratjetoes]

Hi Mustafa,

That's almost what i want. Considering your data sample i would like to return

FIELD_A FIELD_B MAX_DATE
2 1 07-NOV-08
3 2 05-NOV-08
1 3 09-NOV-08

Returns the record per FIELD_B value containing the max date with the appropiate FIELD_A value.

 

[SantaMufasa]

Ah so...here is code that produces that result:

select field_a, field_b, field_date_time
  from table_x
 where (field_b,field_date_time) in
       (select field_b,max(field_date_time)
          from table_x
         group by field_b)
/

 FIELD_A    FIELD_B FIELD_DAT
-------- ---------- ---------
       2          1 07-NOV-08
       3          2 05-NOV-08
       1          3 09-NOV-08

Let us know whether this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[ratjetoes]

that's exactly what i wanted!!

tnx again.

ratjetoes.