I need solution for this problem
-
Hi All,
I need a solution for this problem please help meFirst Create table like below query
create table people (Firstname varchar(50),FamilyName varchar(50),City varchar(50))
then
insert value like Arun, Don, Chennai
insert value like Hema, Don, Chennai
insert value like Kumar, Don, ChennaiI need to display one row for above three rows for that i need a query please help me.
- tvrarundeepak - Saturday, July 28, 2018 9:29 AM
Is this what you need as outputFirstname Firstname Firstname FamilyName City Arun Hema Kumar Don Chennai Saravanan
-
Hi Saravanan,
Output should like this
FirstName FamilyName
Arun Don -
Hi,
Your output doesn't clarify the objective. How are you picking only one record from the set of three records? Is there any filter applied? In case I understood your initial question properly, you can get a comma separated list of first names for each distinct set of family name and cities by this:
SELECT STUFF((SELECT ',' + Firstname FROM dbo.people FOR XML PATH('')),1,1,' ') AS FirstName, FamilyName, City
FROM dbo.people
GROUP BY FamilyName, CityLet us know.
- tvrarundeepak - Saturday, July 28, 2018 10:18 AM
Only you want the below as output:
FirstName FamilyName
Arun DonAre you sure?
Saravanan
- saravanatn - Saturday, July 28, 2018 10:24 AM
yes
- tvrarundeepak - Saturday, July 28, 2018 10:31 AM
like there is n number of familyname and firstname
-
You can simply get it by using TOP 1 like this:
SELECT TOP 1 FirstName, Familyname
FROM dbo.people - tvrarundeepak - Saturday, July 28, 2018 10:32 AM
your explanation is not clear. Can you kindly post expected output?
Saravanan
- saravanatn - Saturday, July 28, 2018 10:37 AM
In the below table we have three family names for example
Output should be like this
Firstname should be randomly selected for each family.
Firstname Familyname city
Mathu Don US
Peri Chennai TN
no Do ys -
Hi,
You can achieve this by using the following query:
SELECT
MAX(Firstname), FamilyName, City
FROM dbo.people
GROUP BY FamilyName, CityBut, it doesn't make good sense to me.
- debasis.yours - Saturday, July 28, 2018 10:57 AM
Thanks for concerns
- tvrarundeepak - Saturday, July 28, 2018 11:00 AM
Its working fine
- tvrarundeepak - Saturday, July 28, 2018 10:52 AMsaravanatn - Saturday, July 28, 2018 10:37 AM
In the below table we have three family names for example
Output should be like this
Firstname should be randomly selected for each family.
Firstname Familyname city
Mathu Don US
Peri Chennai TN
no Do ys
Below query should work I thinkSELECT
MAX(Firstname) over(partition by FamilyName,City order by Firstname) as Firstname,
FamilyName,City
FROM dbo.people;
----------------------------------------
SELECT
MIN(Firstname) over(partition by FamilyName,City order by Firstname) as Firstname,
FamilyName,City
FROM dbo.people;Saravanan
- saravanatn - Saturday, July 28, 2018 11:13 AMtvrarundeepak - Saturday, July 28, 2018 10:52 AMsaravanatn - Saturday, July 28, 2018 10:37 AM
In the below table we have three family names for example
Output should be like this
Firstname should be randomly selected for each family.
Firstname Familyname city
Mathu Don US
Peri Chennai TN
no Do ys
Below query should work I thinkSELECT
MAX(Firstname) over(partition by FamilyName,City order by Firstname) as Firstname,
FamilyName,City
FROM dbo.people;
----------------------------------------
SELECT
MIN(Firstname) over(partition by FamilyName,City order by Firstname) as Firstname,
FamilyName,City
FROM dbo.people;ya it is working for this query
Thanks
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply