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?
Answers were Sorted based on User's Feedback
Answer / grace
SELECT A.*
FROM dbo.T1 A
WHERE CustomerID IN
(SELECT TOP 10 CustomerID FROM dbo.T1 WHERE City=A.City)
Is This Answer Correct ? | 24 Yes | 6 No |
Answer / asim
select id,addressid,city
from
(
SELECT ROW_NUMBER() OVER (partition by city Order by
addressid) ID,AddressID,City
FROM person.Address A
WHERE City IN
(select city from person.address group by City
) )a
where a.ID <11
order by City,ID
Is This Answer Correct ? | 6 Yes | 0 No |
Answer / 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 |
Answer / rakesh ameta
create table newq(ids numeric(5),sname nvarchar(10),city nvarchar(10))
select * from newq where newq.ids in(select top 10 ids from newq x where newq.city=x.city)
Is This Answer Correct ? | 0 Yes | 2 No |
Answer / ravi kumar ravuri
select * from Customer where Customer_ID in (select top 10
from Customer Where City_name in ( select City_Name from
Customer Group by City_Name))
Is This Answer Correct ? | 0 Yes | 4 No |
Answer / abhijith
select top 10
from tablename
where city IN
(select distinct(city) from tablename)
Is This Answer Correct ? | 2 Yes | 6 No |
Answer / abhijith
select top 10 *
from tablename
where city IN
(select distinct(city) from tablename)
Is This Answer Correct ? | 1 Yes | 5 No |
Answer / sonal parekh
select Top 10 CustomerName, city from customer
Is This Answer Correct ? | 5 Yes | 31 No |
How to transfer data from a cursor to variables with a "fetch" statement?
What command is used to rename the database?
How To Find That One Week Change in My DataBase we Have Done Like We Have A database Test I Change Table,Stored Procd. Then I Find Which SP,Table We Have Edit/Change in Seven Dayes
What is BLOCK statements in SQL?
What are the basic functions for master, msdb, model, tempdb and resource databases? : SQL Server Architecture
What do you mean by normalisation?
What is Files and Filegroups in SQL Server & it's implementation.
What is implicit mode in sql server?
What is an inner join?
what are the limitations of parameters in a storedprocedure
What is the parse query button used for?
What are the magic tables in SQL Server 2000?
7 Answers Infogain, Merrill Lynch,