select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

select statement

Author

Topic

blessedame
Starting Member

9 Posts

Posted - 2003-05-13 : 14:14:43

I have a table called history. The table records all notes users enter for a particular ticket. So for each ticket, there could be many notes. So if I have ticketid 12345, I may have notesid 0001 and notesid 0002, each of these notesid has a field called notes_detail.

I need to write a select statement that would only give the latest written note for each ticket. So for ticketid 12345, if notesid 0002 was written today and notesid 0001 was written yesterday, I only want the result of my query to show notesid 0002 and the same for all other ticketids.

I hope this makes sense. Can anyone please help me with this.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-13 : 14:17:57

Chad,

and here's the pitch....

All you babe.

Brett

8-)

blessedame
Starting Member

9 Posts

Posted - 2003-05-13 : 15:22:15

huh? I am not sure I understand the last thread Brett.

thanks

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-13 : 15:41:21

Private joke ....

Break it up into parts:

1. for each ticket, you want the latest note. That is the note with the maximum value in the some date field (I assume):

Select TicketID, MAx(NoteDate) as LatestDate
FROM
TicketNotes
GROUP BY TicketID

2. that doesn't give us much info about the note, just what the date of the latest one is. So, we just join the above to the Notes table and thus have our result:

Select TicketNotes.*
FROM
TicketNotes
INNER JOIN
(SQL statement from #1) A
ON
TicketNotes.TicketID = a.TicketID and
TicketNotes.NoteDate = a.LatestDate

I hope that helps and also I hope it makes sense.

Note: if the latest note is based on NoteID, use that instead of NoteDate in the above queries.

- Jeff

Edited by - jsmith8858 on 05/13/2003 15:43:44

chadmat
The Chadinator

1974 Posts

Posted - 2003-05-13 : 15:42:05

Do you have a datetime on the notes table, or is the latest note always sequentially the highest number?

If you are assuming the highest notesid is always the latest, then you could do:

select ticketid, max(notesid)
from tickets t join notes n on t.ticketid=n.ticketid
group by ticketid

if there is some sort of datetime on the notes table that you didn't tell us about, then you would do:

select ticketid, notesid
from tickets t join notes n on t.ticketid=n.ticketid
join (select ticketid, max(NoteDate) theDate from tickets ti join notes no on ti.ticketid=no.ticketid group by ticketid ) a on n.NoteDate= a.theDate and n.ticketid = a.ticketid

(I think this one will work, I didn't test it)

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

Edited by - chadmat on 05/13/2003 15:45:03

Edited by - chadmat on 05/13/2003 15:50:31

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-13 : 16:03:07

How can something be a private joke in a public forum?

Refer to:

quote:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26159

Brett

8-)

blessedame
Starting Member

9 Posts

Posted - 2003-05-19 : 16:21:44

Thanks, worked perfectly.
I went with Jeff's...

Chad, thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources