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
- 12382 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
Can anyone tell that the extra features are there in SQL SERVER 2008 that are not available in previous versions .
What are the differences between lost updates and uncommitted dependencies?
What is a linked server in sql server?
do you know what is a deadlock and what is a live lock? How will you go about resolving deadlocks? : Sql server database administration
What is full outer join in sql server joins?
What is compound operators?
What is the standby server?
What is amo? : sql server analysis services, ssas
What is difference between foreign key and unique key?
How to stop a loop early with break statements in ms sql server?
What are data regions?
How do you delete duplicate rows in sql server?
How to disable a login name in ms sql server?
What is the difference between clustered index and primary key?
What structure can you implement for the database to speed up table reads?
