Need Help On Multiple Max Values

[gcleffff]

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

[amitpatel66]

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:

[code=sql]

SELECT x.phonenum, y.crc.y.attempt s,x.lastcalled FROM
(SELECT phonenum,max(la stcalled) lastcalled FROM table GROUP BY phonenum) x, table y
WHERE x.phonenum = y.phonenumn
AND x.lastcalled = y.lastcalled

[/code]

[gcleffff]

Try this:

[code=sql]

SELECT x.phonenum, y.crc.y.attempt s,x.lastcalled FROM
(SELECT phonenum,max(la stcalled) lastcalled FROM table GROUP BY phonenum) x, table y
WHERE x.phonenum = y.phonenumn
AND x.lastcalled = y.lastcalled

[/code]

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

[amitpatel66]

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

[cathy25]

Another way of the same query is

Select Phonenum,max(la stcalled) from Table
group by Phonenum
order by Phonenum