Retrieve the last customer record in table

[davidcognos]

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

[MFGF]

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.

[cognostechie]

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

[cschnu]

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.

[blom0344]

Some databases have a pseudo column for every table in the database, for instance Oracle has a column called rownum

Code Select Expand


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)

[cschnu]

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.