topic Re: How to select a distinct row from resident table in Brasil

How to select a distinct row from resident table

Fri, 19 Aug 2011 12:36:34 GMT

Hello,

I have a resident table "TMP1" with:

cod	client_name	initial_date
1	John	01/01/2011
1	Paul	12/12/2010
2	Georgia	05/06/2011
3	Marie	07/30/2011
2	Jack	02/30/2011

But, I need to have just one row for each "cod" depending on the max initial_date.

So, my new table should have:

1 - John - 01/01/2011

2 - Georgia - 05/06/2011

3 - Marie - 07/30/2011

With SQL I could do:

select cod, client_name, initial_date from tmp1 t1

where client_name = (select top 1 client_name from tmp1 t2 where t1.cod = t2.cod order by initial_date desc);

But I need to do this with my resident table and not accessing the database again.

Do somebody have any idea how to do that?

Thanks!!!

Re: How to select a distinct row from resident table

Fri, 19 Aug 2011 13:05:51 GMT

Hi,

See this code.

TMP1:
LOAD
Cod,
Client_Name,
Initial_Date,
Cod & '#' & Initial_Date AS KEY_TMP1;
LOAD
Cod,
Client_Name,
Initial_Date
INLINE [
    Cod, Client_Name, Initial_Date
    1, John, 01/01/2011
    1, Paul, 12/12/2010
    2, Georgia, 05/06/2011
    3, Marie, 07/30/2011
    2, Jack, 02/30/2011
];

// This table filter the max date for each code

SELECT_CLIENT:
LOAD
Cod & '#' & Max_Date AS KEY;
LOAD
Cod,
DATE(MAX(Initial_Date), 'MM/DD/YYYY') AS Max_Date
Resident TMP1
GROUP BY Cod;

// This table using the function "EXISTS" to return just one information for the table TMP1

RESULT:
LOAD
Cod,
Client_Name,
Initial_Date
RESIDENT TMP1
WHERE EXISTS(KEY, KEY_TMP1);

DROP TABLE SELECT_CLIENT;
DROP TABLE TMP1;

Re: How to select a distinct row from resident table

Fri, 19 Aug 2011 14:51:13 GMT

Thanks Eduardo,

You got it!!|!

I will try in my application (it´s a little bit more complicated than the example above).

But I think it will work.