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.
| Author |
Topic |
|
tota00
Starting Member
10 Posts |
 Posted - 2009-08-27 : 06:22:57
|
| Hi!I have an sqlstatement looking like this:SELECT Moduls.Modul_ID, Links.Link_Name, Links.Link_Url, Links.Sort, Links.Target, Portals.Portal_ID, Links.Link_ID FROM Portals INNER JOIN Moduls ON Portals.Portal_ID = Moduls.Portal_ID RIGHT OUTER JOIN Links ON Moduls.Modul_ID = Links.Modul_ID WHERE (Portals.Portal_ID = 45808) ORDER BY Moduls.Sort, Moduls.Modul_Name, Links.Sort, Links.Link_NameOutput is:Modul_ID Modul_Name Link_Name Sort Target Portal_ID Link_Id5187 apa http://www.afton.com 50 0 45808 234165187 hepp http://www.zzz.com 50 0 45808 234155185 Apa http://www.apa.com 50 1 45808 234215185 bepa http://www.bepa.com 50 0 45808 234225185 link1 http://proof.telia.se 50 1 45808 234185184 link2 http://www.dn.se 50 0 45808 234195184 metro http://www.metro.se 50 1 45808 23420What i need is to rewrite the statement so that one more column shows up for example "NrOfModuleId" And that column should in this case have the output like this:NrOfModuleId223332Ie. It´s a counter of how many unique occurrences of Module_Id there is!Hope someone can help cause im stuck...Best // Thomas |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-08-27 : 07:18:16
|
you can add a column to the select list:count(Modul_ID) over (partition by Portal_ID,Modul_Id) as NrOfModuleId
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
tota00
Starting Member
10 Posts |
Posted - 2009-08-27 : 07:37:03
|
quote:
Originally posted by webfred
you can add a column to the select list:count(Modul_ID) over (partition by Portal_ID,Modul_Id) as NrOfModuleId
No i get an error:Incorrect syntax near the keyword 'over'Best // Thomas |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-08-27 : 08:00:15
|
You have posted in Transact-SQL (2005).Which version you're using?
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
tota00
Starting Member
10 Posts |
Posted - 2009-08-27 : 08:10:37
|
quote:
Originally posted by webfred
You have posted in Transact-SQL (2005).Which version you're using?
Sorry for that... Management Studio is 2008 BUT the actual DB i´m working against is 2000...Best // Thomas |
|
|
|
|
|
|
|
|
Help with query