Hi,

Can anyone please help me extract distinct and latest records from a table?

To illustruate, let's say I have the table below:

    ref_no name
      1 santos
      2 garcia
      3 santos
      4 david
      5 bea
      6 garcia
      7 santos

From this table, I would need to have the results below:

   ref_no name
      4 david
      5 bea
      6 garcia
      7 santos

The results must display distinct and lates records based on ref_no.

Thank you so much in advance!

The way approach things like this is to break it down into steps, usually from the "inside out". First, you want to find the set of ref_no where it is a maximum for each name:

SELECT MAX(ref_no) FROM yourtable GROUP BY name;

Them you want the rows in your table where the ref_no is in this set:

SELECT ref_no, name FROM yourtable
WHERE ref_no IN (SELECT MAX(ref_no) FROM yourtable GROUP BY name);

This resultset will be in an arbitrary order, so you may want to order it:

SELECT ref_no, name FROM yourtable
WHERE ref_no IN (SELECT MAX(ref_no) FROM yourtable GROUP BY name)
ORDER BY ref_no;





Jeff Mason
[email protected]

Hi ElyGp!!

Another way to achieve it..
SELECT MAX(ref_no), MAX(name)
FROM dbo.tempTbl
GROUP BY name
ORDER BY MAX(ref_no)

Note-I do recommend you not to use In clause in query for performance reason.
Please let me know if you have any other alternatives
Hope this will help you


Cheers :)

vinod

You are right; the grouping you show above is more efficient than using the IN clause for the specific two column result the OP requested. But, if there were other columns in the table that needed to be retrieved along with the ref_no and name columns, the grouping operation starts to break down.

For example, if the table contained, say, a character string comment column in addition to the two already defined, and the query was to return the comment associated with the max ref_no for each name, the grouping wouldn't work...


Jeff Mason
[email protected]

Thanks to both of you guys!! I really appreciate your suggestions! :)