TSQL Select Third Largest Value

[Beatrix]

Hi Everyone,
I didn't know we have SQL forum on VBAX so I just posted this thread in another forum too.
I am totally a Newbie to T-SQL and need to pass a technical test for my interview. I have done the most of them but stuck with this one below:

Question: Select account name, contact last name, case number, quote number, quote date and quote value for the third largest quote ever created for each of the accounts in the BR2 area.
There are 4 tables: account, contact, case, quote
the query I wrote:


SELECT ac.AccountName, cn.LastName, cs.CaseNumber, q.QuoteNumber, q.QuoteDate, q.Value
FROM Tbl_Account ac LEFT JOIN Tbl_Contact cn
ON ac.AccountID=cn.AccountID
JOIN Tbl_Case cs
ON cn.ContactID=cs.ContactID
JOIN Tbl_Quote q
ON cs.CaseID=q.CaseID
WHERE ac.Postcode LIKE 'BR2%';

I don't know how to select third largest quote ever created for each of the accounts in BR2 area. Can anyone help me with this please?
Cheers
B.

[mancubus]

http://www.programmerinterview.com/index.php/database-sql/find-nth-highest-salary-sql/

For cross-posting etiquette: http://www.excelguru.ca/content.php?184

[Bob Phillips]

I know this thread is a bit stale, and you have probably had the interview, but if it wants the top Nth entry on a table, both the correlated sub-query approach and the more obscure OFFSET FETCH approach are beyond SQL tyro in my view, so if they need that level of skill, as a T-SQL newbie you shouldn't be applying, you will struggle.

[Beatrix]

I know this thread is a bit stale, and you have probably had the interview, but if it wants the top Nth entry on a table, both the correlated sub-query approach and the more obscure OFFSET FETCH approach are beyond SQL tyro in my view, so if they need that level of skill, as a T-SQL newbie you shouldn't be applying, you will struggle.

Hi xld,

there was no time limit for the test and I was told they were testing the approach instead right exact answers as they have developers for that this is an analyst role. I just wanted to do my best even I struggled I learned a lot during this test. My final answer was like below. It was just before Christmas so I haven't had any feedback yet.



SELECT a.AccountName, co.LastName, ca.CaseNumber, q.QuoteDate, q.Value
ROW NUMBER() OVER(ORDER BY q.Value) RowID
FROM Tbl_Account a
INNER JOIN Tbl_Contact co ON a.AccountID=co.AccountID
INNER JOIN Tbl_Case ca ON co.ContactID=ca.ContactID
INNER JOIN Tbl_Quote q ON ca.CaseID=q.CaseID
WHERE RowID=3
GROUP BY a.Postcode LIKE ‘BR2%’;

[Bob Phillips]

Hi xld,

there was no time limit for the test and I was told they were testing the approach instead right exact answers as they have developers for that this is an analyst role. I just wanted to do my best even I struggled I learned a lot during this test. My final answer was like below. It was just before Christmas so I haven't had any feedback yet.



SELECT a.AccountName, co.LastName, ca.CaseNumber, q.QuoteDate, q.Value
ROW NUMBER() OVER(ORDER BY q.Value) RowID
FROM Tbl_Account a
INNER JOIN Tbl_Contact co ON a.AccountID=co.AccountID
INNER JOIN Tbl_Case ca ON co.ContactID=ca.ContactID
INNER JOIN Tbl_Quote q ON ca.CaseID=q.CaseID
WHERE RowID=3
GROUP BY a.Postcode LIKE ‘BR2%’;


All I can say is well done, that looks pretty good to me.

I did have to change it a bit to get it to work with my data, like so


SELECT * FROM
(SELECT a.AccountName
,co.LastName
,ca.CaseNumber
,q.QuoteDate
,q.Value
,ROW NUMBER() OVER(ORDER BY q.Value) RowID
FROM Tbl_Account a
INNER JOIN Tbl_Contact AS co ON a.AccountID=co.AccountID
INNER JOIN Tbl_Case AS ca ON co.ContactID=ca.ContactID
INNER JOIN Tbl_Quote AS q ON ca.CaseID=q.CaseID) AS ac
WHERE RowID=3

[Beatrix]

xld you made my day! It's an honor being told well done by you.. I don't know I'll get this job or not but I am so happy to have this experience.

thanks very much for sharing your knowledge.

Cheers
B.

[Bob Phillips]

I'll keep my fingers crossed for you on the job Beatrix. Maybe I will see you one day at one of the SQL Saturdays, SQLBIts, or other community events like those in the UK (I am in the UK now, disregard the flag).

[Beatrix]

Many Thanks xld. It would be my pleasure to meet you.
I'll have to work really hard If I get this job and I would definitely attend one of those events!