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...

Most of the query cost seems to be in the Key Lookup for DocDataField.FieldValueDateTime in the last outer join in the view.

Try adding a covering index to avoid the lookup.

I...