Dynamic Query - 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)

Dynamic Query

Author

Topic

jstoup111
Starting Member

1 Post

Posted - 2012-05-14 : 20:58:50

Hi I've got a problem and I'm not quite sure the best way to solve this.

I have two tables. A song table and a tag table. This is a one to many relationship. Many tags to one song.

What I need my query to do is select songs from the song table where the tags match ordered by the number of matching tags. Is there a way to do this without doing a complicated Union considering that there is no set limit of the number of tags that a song could have.

Thanks!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-14 : 23:08:41

do you mean this?

 
;With CTE()
AS

(
SELECT ROW_NUMBER() OVER (PARTITION BY Song ORDER BY tag) AS Rn,Song,Tag
FROM table
)

SELECT c1.song
COUNT(CASE WHEN 
FROM CTE c1
INNER JOIN CTE c2
ON c2.tag = c1.tag
AND c2.Rn = c1.Rn
AND c2.song <> c1.song
GROUP BY c1.song,c2.song
ORDER BY MAX(c1.Rn) DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-15 : 05:31:53

quote:
Originally posted by visakh16

do you mean this?

;With CTE()
AS

(
SELECT ROW_NUMBER() OVER (PARTITION BY Song ORDER BY tag) AS Rn,Song,Tag
FROM table
)

SELECT c1.song
COUNT(CASE WHEN
FROM CTE c1
INNER JOIN CTE c2
ON c2.tag = c1.tag
AND c2.Rn = c1.Rn
AND c2.song <> c1.song
GROUP BY c1.song,c2.song
ORDER BY MAX(c1.Rn) DESC

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

If Visakh's above quoted attempt doesn't work then please post some DDL, sample data to go with the DDL and the Expected Result Set.

Please help us in Helping you.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources