i got a table like this
TABLE:
phonenum | crc | attempts | lastcalled
1234 | sd | 5 | 2007-1-08
1234 | ab | 8 | 2006-1-08
2345 | sd | 1 | 2007-1-08
2345 | sd | 5 | 2005-1-06
i need to get the latest date (lastcalled) then the following fields that will return an output like this: it will extract the latest lastcalled date.
OUTPUT:
phonenum | crc | attempts | lastcalled
1234 | sd | 5 | 2007-1-08
2345 | sd | 1 | 2007-1-08
what ive done so far is this:
select phonenum, crc, attempts, max(lastcalled) as lastcalled
from table
group by phonenum,crc, attempts
that query returned a value like this:
phonenum | crc | attempts | lastcalled
1234 | sd | 5 | 2007-1-08
1234 | ab | 8 | 2006-1-08
2345 | sd | 1 | 2007-1-08
2345 | sd | 5 | 2005-1-06
it didnt remove the previous lastcalled.
i need only the latest date and the column corresponds to the latest lastcalled column,
can someone help me thanks
4  1334  
i got a table like this
TABLE:
phonenum | crc | attempts | lastcalled
1234 | sd | 5 | 2007-1-08
1234 | ab | 8 | 2006-1-08
2345 | sd | 1 | 2007-1-08
2345 | sd | 5 | 2005-1-06
i need to get the latest date (lastcalled) then the following fields that will return an output like this: it will extract the latest lastcalled date.
OUTPUT:
phonenum | crc | attempts | lastcalled
1234 | sd | 5 | 2007-1-08
2345 | sd | 1 | 2007-1-08
what ive done so far is this:
select phonenum, crc, attempts, max(lastcalled) as lastcalled
from table
group by phonenum,crc, attempts
that query returned a value like this:
phonenum | crc | attempts | lastcalled
1234 | sd | 5 | 2007-1-08
1234 | ab | 8 | 2006-1-08
2345 | sd | 1 | 2007-1-08
2345 | sd | 5 | 2005-1-06
it didnt remove the previous lastcalled.
i need only the latest date and the column corresponds to the latest lastcalled column,
can someone help me thanks
Try this: -
-
SELECT x.phonenum, y.crc.y.attempts,x.lastcalled FROM
-
(SELECT phonenum,max(lastcalled) lastcalled FROM table GROUP BY phonenum) x, table y
-
WHERE x.phonenum = y.phonenumn
-
AND x.lastcalled = y.lastcalled
-
-
Try this:
-
-
SELECT x.phonenum, y.crc.y.attempts,x.lastcalled FROM
-
(SELECT phonenum,max(lastcalled) lastcalled FROM table GROUP BY phonenum) x, table y
-
WHERE x.phonenum = y.phonenumn
-
AND x.lastcalled = y.lastcalled
-
-
many thanks to you it worked im still a noob at querying can you give me some tips on where to learn more or what book do i need to be a master like you thanks a lot
many thanks to you it worked im still a noob at querying can you give me some tips on where to learn more or what book do i need to be a master like you thanks a lot
Look for some online books on MSSQL and start learning basics
Another way of the same query is
Select Phonenum,max(lastcalled) from Table
group by Phonenum
order by Phonenum
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: Jack |
last post by:
I have a text file of data in a file (add2db.txt) where the entries are
already entered on separate lines in the following form:
INSERT INTO `reviews` VALUES("", "Tony's", "Lunch", "Great...
|
by: Darren Dale |
last post by:
Hello,
def test(data):
i = ? This is the line I have trouble with
if i==1: return data
else: return data
a,b,c,d = test()
|
by: John Smith |
last post by:
I have a user assigned multiple roles and a role can be inherited from
multiple parents (see below). How do I answer such questions as "How
many roles does the user belongs to?"
I answered the...
|
by: Steve |
last post by:
i pulled an example off the web and modified it somewhat and whats killimg
me is that sometimes it works and sometimes it doesnt.
the following is the only line that allows this thing to work over...
|
by: Tom |
last post by:
I am looking for some ideas for how to design the layout of the form for
data entry and to display the data for the following situation:
There are many sales associates. A sales associate can work...
| | |
by: Adam |
last post by:
I am coding a microkernel based off of Tanebaum's theroy. For Isis to
be extensible, fast, and secure, it has been decided it will be a
microkernel. Not in the old Mach sense of the word, but in...
|
by: Jeff Davis |
last post by:
Right now performance isn't a problem, but this question has me curious:
Let's say I have a shopping cart system where there is a "products"
table that contains all possible products, and an...
|
by: Richard Lewis Haggard |
last post by:
We are having a lot of trouble with problems relating to failures relating
to 'The located assembly's manifest definition with name 'xxx' does not
match the assembly reference" but none of us here...
|
by: rich |
last post by:
I have a database with 1 to many and the many is a list with multiple
selects in a list. When I click on a master record I have as part of
my form the select statement for the multiple choice...
|
by: v4u2chat |
last post by:
Do I need to extend any of classes from AXIS to return multiple values? I'm exposing the following method as web service through AXIS to return multiple values.
public ContactAddress...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
