sql query logic - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

sql query logic

Author

Topic

hardikspider123
Starting Member

12 Posts

Posted - 2014-01-17 : 23:57:55

In my data city has preference numbers and name of the people who lived in more then one city but in data export if person is listed under one city same record should not be repeated for other city.there is a big dataset all should follow pattern of city sequence. how would i write query what would be the logic

for example john smith has lived in jercy city and in newyork. jersey city rank is 1. so his record should be selected once for jercey city.

All record should follow this same pattern.

How would I write this query in sql? what would be the logic.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-01-18 : 12:10:55

You would select TOP(1) from the cities associated with the person. For example

SELECT
   t1.person,
   t2.PrimaryCity
FROM
   YourTable t1
   cross apply
   (SELECT TOP (1) City AS PrimaryCity
    FROM YourPersonCityTable p
    ORDER BY CityRank
   ) t2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-19 : 02:54:33

quote:
Originally posted by James K

You would select TOP(1) from the cities associated with the person. For example
SELECT
t1.person,
t2.PrimaryCity
FROM
YourTable t1
cross apply
(SELECT TOP (1) City AS PrimaryCity
FROM YourPersonCityTable p
WHERE PersonID = t1.PersonID
ORDER BY CityRank
) t2

should include some condition like above based on relationship

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources