How to get the last value in a query

I have a query that checks status of faxes and whether the fax was sent successfully or not. I would like to get the very last status of the fax attempt to make a better report. Here's is what I would need:

Fax ID     Fax Number     Status
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

Would turn into....

Fax ID        Fax Number       Status
1                5551234567       Loop  
2                1234567890       Success
3                1235556789       No Dial Tone                
4                 9871234567      Success
5                 1112223333      Success
6                 2223334444      Success

Not sure if this could be better done with MSSQL or Excel... Thanks.
horaliaAsked:
Who is Participating?
 
barletConnect With a Mentor Commented:
try this:

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

Open in new window

0
 
Martin LissOlder than dirtCommented:
You can use Select Distinct.
0
 
barletCommented:
as MartinLiss said you should use distinct command,
in your case would be:

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

Open in new window

0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
horaliaAuthor Commented:
But wouldn't that still give me all Status? I only want the last one within the Fax ID.
0
 
Anthony PerkinsCommented:
>>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.
0
 
Anthony PerkinsCommented:
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

Open in new window

0
 
Anthony PerkinsCommented:
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')

Open in new window

Otherwise you are relying on some poor schmuck (as in yours truly) do have to build it.
0
 
tliottaCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.