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
- 10756 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
Explain different forms of normalization?
What are the operating modes in which database mirroring runs?
How do I trace a query in sql server?
What is the data tier application?
What are the different types of cursor?
Explain what is raid and what are different types of raid levels?
Which is better statement or preparedstatement?
Explain how to maintain a fill factor in existing indexes?
What is report snapshot in ssrs?
What are 3 ways to get a count of the number of records in a table?
what is checksum in sql server.........???
How do I find sql server instance name?
difference between Clustered index and non clustered index ?
What is the xml datatype?
What is the difference between stored procedure and user defined functions?
