Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!!!
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;
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;
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.