There is a cheat if you only want to return the first row within each partition, but it uses an additional sort, so it's actually more expensive than using the...

The WHERE clause is evaluated before the SELECT clause (which is where the ROW_NUMBER is defined), therefore the WHERE cannot reference the ROW_NUMBER in the same (sub)query where the ROW_NUMBER...

SQLBill (9/14/2016)

---

While probable not quite accurate...the way to think about is this way...when using a single # think of it as being owned by the schema that made it:

Conn1.temp01

Conn2.temp01

When using...

The Dixie Flatline (9/15/2016)

---

What about those two tables is not normalized?

select p.id, p.FirstName, p.LastName, p.dob, pf.pos,pf.Hobby, pf.Color, pf.SQLConference

from @Person p

join @Person_Favorites pf on p.ID = pf.ID

--where pf.SQLConference like 'SQL%'

--where color...

Michael L John (9/15/2016)

---

It also appears that you may have a logic error in your code:

WHERE

CONVERT(varchar(20), H.TransactionDate, 101) >= @StartDate

AND CONVERT(varchar(20), H.TransactionDate, 101) <= @EndDate

If...

Both Alan.B and Gazareth started with the summarized data. Here is how you would do it starting with the raw data.

DECLARE @table TABLE (stateName varchar(20), Amount int);

INSERT @table

VALUES

('Alabama',200),

('Alabama',200),

('Alabama',200),

('Alabama',200),

('Alabama',200),

('Arizona',2000),

('Arizona',2000),

('Arkansas',2000),

('Arkansas',2000),

('Arkansas',1000)

;

SELECT...

John Mitchell-245523 (9/15/2016)

---

Looks fairly simple - you're just joining all three tables together, and using a MAX or MIN to get the top sale per invoice. Is there any...

A TOP(n) requires an order, which you haven't specified, but there is only one field that will give your specified results, so I have used that field.

SELECT InvRef, Amount, Date,...

j-1064772 (9/15/2016)

---

tripleAxe (9/15/2016)

---

Some basic testing on my VM using the original table as posted but with 12 million rows of data has the UNION ALL query taking 6 seconds with...

Clean up your data before submitting it to your function.

DECLARE @input VARCHAR(250) = 'abc,def,ghi,123,jkl,345,678,mno,901,234,567,pqr'

WHILE PATINDEX('%[0-9],[0-9]%', @input) > 0

SET @input = STUFF(@input, PATINDEX('%[0-9],[0-9]%', @input) + 1, 1, '')

You should also change...

This is his most recent question:...

Jason A. Long (9/14/2016)

---

Jeff Moden (9/14/2016)

---

Although that's great use of the CROSS APPLY trick to unpivot, the output isn't the same as the requested output, which appears to be a...

I think someone is in way over their head. Maybe we should just tell him to find another line of work.

Drew

Dude! This goes well beyond the scope of a free forum.

Drew

This is T-SQL, not some idealized theoretical version of SQL. T-SQL...