Find third largest quote ever created for each of the accounts in the EC1 area

Question

Can anyone help I'm new to SQL and trying to figure out the below question see image for the table structure;

Question = Select account name, contact last name, case number, quote number, quote date and quote value for the f third-largest quote ever created for each of the accounts in the EC1 area

So far I got;

Select 
    a.accountname, cc.lastname, c.casenumber, 
    q.quotenumber, q.quotedate, q.quotevalue 
from 
    TBL_Quote q 
Left join 
    TBL_case c On q.caseid = c.caseid 
Left join 
    tbl_contact cc On c.contactID = cc. contactID 
Left join    
    tbl_account a On a.accountid = cc.accountid 
Where 
    left(a.postcode, 3) like 'EC1' 

and for the third:

SELECT TOP 1 value 
FROM 
    (SELECT DISTINCT TOP 3 value 
     FROM tbl_quote 
     ORDER BY value DESC) a 
ORDER BY value 

I can't seem to combine the top 3 and the query is it best to overpartion by ?

Answer 1

I would suggest joins and a row-limiting clause:

select ac.accountName, co.lastName, ca.caseNumber, qu.quoteNumber
from tbl_account       ac
inner join tbl_contact co on co.accountId = ac.accountId
inner join tbl_case    ca on ca.contactId = co.contactId
inner join tbl_quote   qu on qu.caseId    = ca.quoteId
where ac.postcode like 'EC1%'
order by len(qu.value) desc
offset 2 rows fetch next 1 row only