A table contains list of customers and his city with other
details. Each customer has a unique number and the table
consists millions of data. Query is: I want to retrieve 10
customers from each city, no script, only from single query?
- 8 Answers
- 11166 Views
- Infosys, I also Faced
- E-Mail Answers
Answer Posted / jyoti
CREATE TABLE #CustDetails
(
CustID INT IDENTITY(1,1),
CustName VARCHAR(10),
City VARCHAR(10)
)
INSERT INTO #CustDetails
SELECT 'A1','Pune' UNION ALL
SELECT 'A2','Pune' UNION ALL
SELECT 'A3','Pune' UNION ALL
SELECT 'A4','Pune' UNION ALL
SELECT 'A5','Pune' UNION ALL
SELECT 'A6','Pune' UNION ALL
SELECT 'B1','Delhi' UNION ALL
SELECT 'B2','Delhi' UNION ALL
SELECT 'B3','Delhi' UNION ALL
SELECT 'B4','Delhi' UNION ALL
SELECT 'B5','Delhi' UNION ALL
SELECT 'B6','Delhi' UNION ALL
SELECT 'B7','Delhi' UNION ALL
SELECT 'B8','Delhi' UNION ALL
SELECT 'C1','Mumbai' UNION ALL
SELECT 'C2','Mumbai' UNION ALL
SELECT 'C3','Mumbai' UNION ALL
SELECT 'C4','Mumbai' UNION ALL
SELECT 'C5','Mumbai'
SELECT
ID,CustName,City
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY CITY ORDER
BY CustID DESC) ID,CustName,City
FROM
#CustDetails AS CD1
) A
WHERE A.ID < 3
| Is This Answer Correct ? | 4 Yes | 0 No |
Post New Answer View All Answers
tell me what are the steps you will take to improve performance of a poor performing query? : Sql server database administration
What is the main purpose of having conversation group?
What is the difference between a function and a trigger?
Can I save my report as html, excel or word? : sql server management studio
What is awe?
What are the extra roles available in msdb? : sql server security
What does the automatic recovery do?
How to use user defined functions in expressions?
If a user does not have permission to a table, but has permission to a view created on it, will he be able to view the data in table?
Explain “@@rowcount” and “@@error” in sql server?
What are the reporting services components?
Which are the new data types introduced in sql server 2008?
How do I view a stored procedure in sql server query?
What is difference between oltp and olap?
What is a document index?
