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
- 10121 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 are the rendering extensions of ssrs?
What is the name of the Database which IBM mainframe uses?
State a few properties of relational databases?
How to use subqueries with the exists operators in ms sql server?
What are different types of data sources?
Does view occupy space?
what's sql server? : Sql server database administration
How to create user messages with print statements in ms sql server?
what are different types of backups available in sql server? : Sql server database administration
What is a filestream?
What are the different types of columns types constraints in the sql server?
How do indexes help, types?
How to use “drop” keyword in sql server and give an example?
What is the difference between functions and stored procedures?
Can a cursor be updated? If yes, how you can protect which columns are updated?
