Hi,
We have the requirement that is to retrieve the last customer record in the table. Here it is not based on the timestamp or the date as this record is updated daily. One customer no will have more than one account. Following is the data in the table:
CTRY_CDE GROUP_MEMBR_CDE COLL_LOC_CDE CUST_IP_NUM ACCT_NUM
-------- --------------- ------------ ------------------------------ ------------------
UJ HSBC 205093 DFG91 PHHBPH050004444493
UJ HSBC 205094 DFG92 PHHBPH050004444491
UJ HSBC 205095 DFG93 PHHBPH050004444495
UJ HSBC 205095 DFG93 PHHBPH050004444496
UJ HSBC 205093 DFG90 PHHBPH050004444472
UJ HSBC 205093 DFG90 PHHBPH050004444477
From the above data, the requirement is that we have to retrieve the last customer i.e. DFG90 records in the table.
The output data should be as follows
CTRY_CDE GROUP_MEMBR_CDE COLL_LOC_CDE CUST_IP_NUM ACCT_NUM
-------- --------------- ------------ ----------------------- ------------------
UJ HSBC 205093 DFG90 PHHBPH050004444472
UJ HSBC 205093 DFG90 PHHBPH050004444477
Can anybody please help us in achieving in Congos.
Thanks in Advance!!!
Regards
Hi,
This is probably not as straightforward as you imagine. Assuming the rows of data are stored in a relational table, there is no set order in which they will be retrieved (unless you sort the query), so how can you logically identify the 'last' one? It may well have been the first one entered, but could quite easily be displayed last in the result set depending on how the database query optimiser structured the query. A similar but slightly different query could show the rows in totally different positions in the result set, so there is no guaranteed consistency either (again, unless you sort the query).
Is there some other value that you could use to determine an appropriate sequence? If so, then you could perhaps use a query to get the maximum() value of that item, and then join it back to your main query.
Regards,
MF.
Like MFGF said, try to identify a field in the table which would indicate which one
is the last record for that customer.
In most cases, there is always a time stamp to indicate when the record was created. This is
usually a field called 'Update_Time' or 'Update_Date' which might be a DateTime format or something like that. If you have that kind of field, you could do something like this:
Select CUST_IP_NUM , max(<Update_Time>) as 'Last Update Date-Time'
from <Table-Name>
Regards
Like previous posts suggested if you have a column like a surrogate key that you could get the maximum value from that should give you that last row.
Quote from: cschnu on 17 Sep 2010 02:18:05 PM
Some databases have a pseudo column for every table in the database, for instance Oracle has a column called rownum
select max(rownum)
from employee
Also, like previous posts suggested if you have a column like a surrogate key that you could get the maximum value from that should give you that last row.
cschnu,
You are (very much) mistaken about the rownum pseudo column. The values assigned are based on the
order implied by the query to retrieve the data.
ref: http://www.adp-gmbh.ch/ora/sql/rownum.html
As MFGF explained relational databases have no inherent storage order...
To line up data, you either perform a sorting or a ranking. Ranking data can be achieved using OLAP functions in the database (Oracle since 9i, DB2 since 7.1, SQL server since 2005)
Quote from: blom0344 on 17 Sep 2010 02:46:59 PM
cschnu,
You are (very much) mistaken about the rownum pseudo column. The values assigned are based on the order implied by the query to retrieve the data.
ref: http://www.adp-gmbh.ch/ora/sql/rownum.html
As MFGF explained relational databases have no inherent storage order...
To line up data, you either perform a sorting or a ranking. Ranking data can be achieved using OLAP functions in the database (Oracle since 9i, DB2 since 7.1, SQL server since 2005)
Yes, you are correct sorry for the bad information, i have edited out my previous comment.