Interested to Buy Any Domain ? << Click Here >> for more details...
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
- 12760 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
What is stored in the mssqlsystemresource database? : sql server database administration
How to make a column nullable?
Explain how you can configure a running aggregate in SSRS?
Explain how you can deploy an SSRS report?
Write an sql query for deleting duplicate rows?
What do you understand by intent locks?
What is the difference between online clustering and Offline clustering?
Do I need a report server to run reports in my application?
Can we update data in a view?
What is the difference function and stored procedure?
How to create a trigger for insert only?
How to find related tables in sql server?
What protocol does sql server use?
Why truncate is ddl command?
List some case manipulation functions in sql?
