MSSQL FIRST() aggregate function

I've been staring this one in the face for a while now..

Does anyone know how to do this sort of query strictly in sql? (not t-sql, pl/sql)

I need a query like this as a subquery for another query in mssql

SELECT FIRST(phone.phone)
FROM   phone
GROUP BY phone.contact_id
ORDER BY phone.last_update DESC

Code (SQL):

or

SELECT TOP 1 phone.phone
FROM   phone
GROUP BY phone.contact_id
ORDER BY phone.last_update DESC

Code (SQL):

Soooo obviously this won't work because FIRST doesn't work in mssql and TOP and GROUP BY don't groove together

But what i'm looking for is the most recently updated phone for each contact in the db..

why wouldn't mssql just allow me to use first.. i hate it

 

Try using a nested select where you join on contact_id and select by the last_update date. Something like this.

SELECT p1.contact_id, p1.phone
FROM  phone p1
where p1.last_update = 
[INDENT](select max(p2.last_update) from phone p2 
where p1.contact_id = p2.contact_id
group by p2.contact_id)
[/INDENT]

Code (markup):

I don't have a table that has detail records like you've got so I can't test it...but I think that will work.

 

SELECT phone.phone
FROM   phone
GROUP BY phone.contact_id
ORDER BY phone.last_update DESC limit 1

Code (markup):

 

This wont work correctly in MSSQL. Limit is a MySQL command.

Is there a reason why this doesn't work?

SELECT phone.phone
FROM phone
GROUP BY phone.contact_id
ORDER BY phone.last_update DESC

This should return only a single phone for each contact id sorted by phone.last_update

You can use it like:

SELECT phone.otherstuff
FROM phone
WHERE phone.phone IN (

SELECT phone.phone
FROM phone
GROUP BY phone.contact_id
ORDER BY phone.last_update DESC

)