MSSQL FIRST() aggregate function

Discussion in 'MySQL' started by Jamie18, May 7, 2009.

0
  1. #1
    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
     
    Jamie18, May 7, 2009 IP
  2. freelistfool

    freelistfool Peon

    Messages:
    1,801
    Likes Received:
    101
    Best Answers:
    0
    Trophy Points:
    0
    #2
    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.
     
    freelistfool, May 7, 2009 IP
  3. javaongsan

    javaongsan Well-Known Member

    Messages:
    1,054
    Likes Received:
    7
    Best Answers:
    0
    Trophy Points:
    128
    #3 
    
SELECT phone.phone
FROM   phone
GROUP BY phone.contact_id
ORDER BY phone.last_update DESC limit 1
    Code (markup):
     
    javaongsan, May 7, 2009 IP
  4. jestep

    jestep Prominent Member

    Messages:
    3,659
    Likes Received:
    215
    Best Answers:
    19
    Trophy Points:
    330
    #4
    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

    )
     
    jestep, May 8, 2009 IP