How to get the last value in a query

You can use Select Distinct.

as MartinLiss said you should use distinct command,
in your case would be:

select distinct(Fax ID), Fax Number, Status from your_table

Select allOpen in new window

But wouldn't that still give me all Status? I only want the last one within the Fax ID.

>>I would like to get the very last status of the fax attempt to make a better report. <<
Unfortunately, you have not defined what is the "very last status".  The previous solution will give you the highest alphabetical Status in this order: Success, No Dial Tone, Loop.  Is that really what you want?  I supect you have another column which provides a datestamp of when the entry was made.  Failing that some sequence number that we can use.

Incidentally the solution posted here http:#a37833813 will either require an aggregate funtion on Fax Number or it will have to be added to the GROUP BY clause (an ORDER BY would also help) as in :

select Fax ID, Fax Number, MAX(Status)
from YourTable
group by Fax ID, Fax Number
ORDER BY Fax ID, Fax Number

Select allOpen in new window

Also if you post your question with a complete script as follows, you will find members here far more responsive:

DECLARE @YourTable TABLE (
		FaxID integer NOT NULL,
		FaxNumber varchar(20) NOT NULL,
		[Status] varchar(20) NOT NULL
		)
		
SET NOCOUNT ON

INSERT	@YourTable(FaxID, FaxNumber, Status)
VALUES  (1, '5551234567', 'LOOP'),
	(1, '5551234567', 'Loop'),
	(1, '5551234567', 'Loop'),
	(2, '1234567890', 'No Dial Tone'),
	(2, '1234567890', 'Success'),
	(3, '1235556789', 'No Dial Tone'),
	(3, '1235556789', 'No Dial Tone'),
	(4, '9871234567', 'No Dial Tone'),
	(4, '9871234567', 'Success'),
	(5, '1112223333', 'Success'),
	(6, '2223334444', 'Success')

Select allOpen in new window

Otherwise you are relying on some poor schmuck (as in yours truly) do have to build it.

It's not clear (1) what kind if file is being queried, nor (2) if any other fields are available in each record. As noted earlier, by acperkins, you have not defined what is the "very last status".

As humans, we can pretty much figure out that you mean the last record displayed from each group of Fax IDs. But that's primarily because you supplied us with an ordered set of rows. We don't know how you chose them to be in the order that you displayed.

SQL cannot make any similar assumptions. If you want the last row from a group, you have to tell SQL how to sort the records. Usually, that means that you have some field that acts as a "sequence number". None of the three fields that you showed us will work for that. You displayed three records that have Fax ID=1, but SQL has no way to know which one ought to be the first of the three and which should be the last.

It's probable that the records you showed us are the result of a SQL query. Therefore, you might reasonably figure that SQL already knows the order that you need.

But it's not guaranteed.

Depending on how the file is defined and how updates, deletions and additions are made to the file as well as how the data space is managed and perhaps other factors, it's quite possible that the same query will give a different order on a different day. (Unless perhaps  there are more fields that we don't know about.)

In short, it might be possible to do. But we can't know yet if it is reasonably possible nor if there is an easy way to do it. We don't know enough about the file. And whatever answer we give, based on what we know so far, might not work a week from now.

The file so far just doesn't support the need.

Tom