Tags

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,


SQL PUZZLE | Get MAX amt Data for each customer using SINGLE SELECT | SQL Interview Question

This question was asked to me in a recent technical discussion with an associate developer. In this puzzle you have to get the maximum amount data for each customer. The challenge here is that you can only use a single SELECT keyword. Please check out sample input and expected output.

Sample Input

Id customer Amt
1 Pawan 2
2 Vaibhav 3
3 Pawan 5
4 Vaibhav 1

Expected output

Id customer Amt
3 Pawan 5
2 Vaibhav 3

Script – DDL and INSERT sample data

--

CREATE TABLE Amounts 
(
	  Id INT
	, customer Varchar(10)
	, Amt INT
)
GO

INSERT INTO Amounts VALUES 
(1 ,'Pawan',2),
(2 ,'Vaibhav',3),
(3 ,'Pawan',5),
(4 ,'Vaibhav',1)
GO

SELECT * FROM Amounts
GO

--

SOLUTION 1 | USING SINGLE SELECT

--

SELECT b.* FROM Amounts a
LEFT JOIN Amounts b
ON a.customer = b.customer 
AND a.Amt < b.Amt WHERE b.Id IS NOT NULL

--

OUTPUT

--

Id          customer   Amt
----------- ---------- -----------
3           Pawan      5
2           Vaibhav    3

(2 rows affected)



--

SOLUTION 2 | USING 2 SELECTs & MAX Function

--

SELECT a.Id,o.* 
FROM Amounts a
INNER JOIN
(
	SELECT customer,MAX(Amt) Amt
	from Amounts
	GROUP BY customer
)o ON a.customer = o.customer AND a.Amt = o.Amt

--

OUTPUT

--

Id          customer   Amt
----------- ---------- -----------
3           Pawan      5
2           Vaibhav    3

(2 rows affected)



--

SOLUTION 3 | USING 2 SELECTs & CROSS APPLY

--


SELECT n.* FROM 
(
	SELECT DISTINCT customer
	FROM Amounts
)x
CROSS APPLY
(
	SELECT TOP 1 * FROM Amounts a
	WHERE a.customer = x.customer
	ORDER BY Amt DESC
)n

--

OUTPUT

--

Id          customer   Amt
----------- ---------- -----------
3           Pawan      5
2           Vaibhav    3

(2 rows affected)


--

SOLUTION 4 | USING Row_NUMBER with ORDER BY ( 2 SELECTS )

--


SELECT Id,customer,Amt FROM 
(
	  SELECT * 
	, ROW_NUMBER() OVER(PARTITION BY customer ORDER BY Amt DESC) rnk FROM Amounts
)x WHERE x.rnk = 1
GO

--

OUTPUT

--

Id          customer   Amt
----------- ---------- -----------
3           Pawan      5
2           Vaibhav    3

(2 rows affected)

--

Enjoy 🙂

Please add comment(s) if you have one or multiple solutions in mind. Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com