Youngest Note Select

Question

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John McC Hall of Fame Points: 3410 More actions · Answer 1

How would you differentiate between the two 2TH accounts on 09/02/2010?

-------------------------------------------------------------------------
Normal chaos will be resumed as soon as possible. :crazy:

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

One method , untested but should be ok

with cteNotes

as(

select *,ROW_NUMBER() over (partition by Account order by Date desc) as Rown

from table

)

select * from cteNotes where Rown =1

--EDIT : But John has raised a very important question , this will get one of those rows 'at random'

Clear Sky SQL
My Blog[/url]

j.dibble SSChasing Mays Points: 618 More actions · Answer 3

Great Thanks - will run the code and let you know the outcome.

As for John's valid point - I don't think we will be able to as the data exported does not provide a time, only the date.

j.dibble SSChasing Mays Points: 618 More actions · Answer 4

Just checked the extracted table and found that there is a TIME element field (new table attached)

Can this be used to ensure the youngest NOTE is returned?

Thanks for all your help.

j.dibble SSChasing Mays Points: 618 More actions · Answer 5

Please ignore last note - was being an idiot I just needed to change the code to :

with cteNotes

as(

select *,ROW_NUMBER() over (partition by Account order by Date desc,Time asc) as Rown

from dbo.TelesalesLastNote )

select * from cteNotes where Rown =1

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 6

Watch your ordering !

Date desc,Time asc

Will return the first one on the last day,

Date desc,Time desc

Will return the last one on the last day

Clear Sky SQL
My Blog[/url]

Paul White SSC Guru Points: 150467 More actions · Answer 7

As a side-note:

If you use RANK rather than ROW_NUMBER, you will at least always get the same result for the same input.

This is the 'WITH TIES' problem. RANK will return all tied rows, ROW_NUMBER would choose one, in a non-deterministic manner.

Use ROW_NUMBER if you absolutely must only get one row, and have no deterministic way to add a tie-breaker.

Paul