Solved

How to get the last value in a query

Posted on 2012-04-11
8
192 Views
Last Modified: 2012-04-25
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.
0
Comment
Question by:horalia
8 Comments
 
LVL 45

Expert Comment

by:Martin Liss
ID: 37833166
You can use Select Distinct.
0
 
LVL 7

Expert Comment

by:barlet
ID: 37833735
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
 

Author Comment

by:horalia
ID: 37833754
But wouldn't that still give me all Status? I only want the last one within the Fax ID.
0
 
LVL 7

Accepted Solution

by:
barlet earned 500 total points
ID: 37833813
try this:

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

Open in new window

0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37835808
>>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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37835822
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37835833
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
 
LVL 27

Expert Comment

by:tliotta
ID: 37840894
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

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now