http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/rowset-string-concatenation-which-method-is-best.aspx

Here's another one to add to the pile...

; WITH cteRankedAddresses AS (

SELECT AccountNum, MAX(DM_Source_ID) AS MaxDM_Source_ID, Address,

ROW_NUMBER() OVER...

Like this?

...order by case when PageId > 0 then 1 end desc, StatusID, PageID

Hi Chris

Yes, you're right. I was going by the wording by in the examples post, but reading back I see this is needed for each account number. My apologies.

Possibly I'm oversimplifying, but the following meets my understanding of the requirements...

select top 1 Address from myTable group by Address order by count(*) desc

Only by upgrading to 2005...

Hehe - lowly indeed. There's your problem.

Some reading material...

http://sqlblogcasts.com/blogs/jonsayce/archive/2007/12/03/using-where-not-in-select-with-nulls.aspx

http://drsql.spaces.live.com/blog/cns%2180677FB08B3162E4%21777.entry

http://sqlblog.com/blogs/hugo_kornelis/archive/2007/09/22/dr-unknown-or-how-i-learned-to-stop-worrying-and-love-the-null.aspx

There's not a null userid in dbo.users, is there?

Something along these lines...

select convert(varchar(8), getdate(), 112) + '_' +

replace(substring(convert(varchar(20), getdate(), 113), 13, 8), ':', '-')

Here's one way...

select * from @mytable a where 2 >

(select count(*) from @mytable where GroupKey = a.GroupKey and field1 < a.field1)

Jeff Moden (7/21/2008)

---

Yes, you can... you'll loose performance, as well. 😉 It turns out that Cross Tabs are generally quicker than Pivots especially if you preaggregate the data...

Jeff Moden (7/19/2008)

---

--==== Pivot the data using the MAX aggregation.

SELECT MAX(CASE WHEN RemarkType = 'RemarkType1' THEN Remarks ELSE ' ' END) AS RemarkType1,

...

Ralph?

The error is because you need square brackets around 'Extended Price' on line 4.

SUM([Extended Price]) AS [Total Extended Price],

What's the structure of the XML?