max(seq) over (partition by street)

returns the maximum sequence number in each street group.

In case of ties between different streets, you order by street.

Inside each street group, you want the...

You probably need to keep the streets together. Try adding street in the order by in 2nd position:

select *

from @jobs

order by max(seq) over (partition by street) desc, street, seq...

To understand the query you just have to add the order by column to the select list:

select *, max(seq) over (partition by street) as maxseq

from @jobs

This returns the max sequence,...

select * from @jobs

order by max(seq) over (partition by street) desc, seq desc

Hope this helps

Gianluca

This should do the trick. There must be some quicker way, but for a one time search it should work.

DECLARE @searchString varchar(100)

DECLARE @searchStringEscaped varchar(100)

SELECT @searchString = ' XYZ_',

@searchStringEscaped = '%...

You can do it yourself: https://www.sqlservercentral.com/MyAccount

Thanks Gus. I'll be sure to test it, obviously.

I read on a quite old article that partitioning makes reads worse, but improves writes. Is it true in your experience?

Also, will...

GilaMonster (2/18/2010)

---

Is it me or is the signal:noise ratio here getting worse?

Some threads lately there's been so much conflicting advice from various people that it's hard to get useful...

jcrawf02 (2/18/2010)

---

Grant Fritchey (2/18/2010)

---

Oh jeez, I'm so stupid. I never even thought about that. No wonder everyone gets so many points. Here I am trying to honestly answer the questions......

Duplicate post, deleted

GilaMonster (2/18/2010)

---

Logical order of the index - yes

Physical order of the data - no

Order...

In that particular case, with the identity column as primary key clustered, yes, doesn't...

It could be a date format issue. Your code is also prone to sql injection, use prepared statements instead.

Put @parameter placeholders in your sql, then add parameter values to your...

Looks like somebody can't stay away from corruption threads...

http://www.sqlservercentral.com/Forums/FindPost867727.aspx

I foresee SSIS being mentioned 😀

It's hard to tune a query without table definition scripts and indexes.

That said, you could try to get rid of one of the outer joins in your view:

CREATE VIEW [dbo].[viMostRecentPatientEvent]

AS

SELECT...