Solved

How to get the last value in a query

Posted on 2012-04-11
8
234 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 47

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
Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

 
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
 
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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

707 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