1. Categories >> Software >> Databases >> SQL Server
  2. Suggest New Category

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?

Question Posted / jyoti

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


Please Help Members By Posting Answers For Below Questions

How do I get Report Builder to generate a parameter that can be set by users viewing the report?

100

What are the new features of sql server 2008 r2 reporting service?

110

What is a unique key constraint?

522

What is the difference between MVC and Teir Architecher? Plz explain with Layyered Programming example...? Thanks

1607

Explain the difference between HTTP and HTTPS in database?

555




I have all the primary data files, secondary data files as well as logs. Now, tell me can I still restore the database without having a full backup? : sql server database administration

669

What are the 7 disadvantages to a manual system?

499

How can I create a new template for import ? : sql server management studio

587

how you can configure a running aggregate in SSRS?

112

What is the difference between a view and a stored procedure?

544

What is #table in sql server?

526

What is temporary stored procedure?

542

List some case manipulation functions in sql?

585

What do you understand by coalesce in sql server?

493

How to insert and update data into a table with "insert" and "update" statements?

502