Finding last record - 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

Transact-SQL (2000)

Finding last record

Author

Topic

domo
Starting Member

23 Posts

Posted - 2008-04-09 : 09:21:25

I have 3 columns, TicketID, HistoryNO and Description which look like this:

8 01 Description
8 02 Description
8 03 Description
8 04 Description
9 01 Description
9 02 Description

The table is joined to other tables using TicketID. I only want to report on the last HistoryNO for each TicketID. I've tried using MAX(dbo.TicketNO) but this doesn't work.

I would be very grateful for some help

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-09 : 09:24:16

what datatype is historyID?

select ticketID, max(cast(historyID as int))
from yourtable
group by ticketID

Em

domo
Starting Member

23 Posts

Posted - 2008-04-09 : 09:26:41

Hi Em,

Data type is INT.

domo

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-09 : 09:28:31

really? then how come it's got a leading zero?
in that case just...

select ticketID, max(historyID)
from yourtable
group by ticketID

Em

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-09 : 09:29:16

quote:
Originally posted by domo

Hi Em,

Data type is INT.

domo

If it is INT, then you dont need cast

select ticketID, max(historyID)
from yourtable
group by ticketID

Madhivanan

Failing to plan is Planning to fail

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-09 : 09:29:45

Madhivanan

Failing to plan is Planning to fail

domo
Starting Member

23 Posts

Posted - 2008-04-09 : 09:35:48

quote:
Originally posted by elancaster

really? then how come it's got a leading zero?

Sorry - my mistake, I keyed those values into the post! Its definitely Int.

quote:
Originally posted by madhivanan

If it is INT, then you dont need cast

select ticketID, max(historyID)
from yourtable
group by ticketID

Thanks for your help guys.

domo
Starting Member

23 Posts

Posted - 2008-04-09 : 10:02:28

I cannot seem to add additional fields to the select statement.
For example:

select ticketID, max(historyID), Details
from yourtable
group by ticketID

Error:
Msg 8120, Level 16, State 1, Line 1
Column 'Details' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-09 : 10:04:35

you either need to group by it too or do something like...
select t2.*
from
(
select ticketID, max(historyID) hist
from yourtable
group by ticketID
) t1
join yourTable t2 on t1.hist = t2.historyID and t1.ticketID = t2.ticketID

Em

domo
Starting Member

23 Posts

Posted - 2008-04-09 : 10:32:05

Here's the actual code so far. I'm trying to work out how I can use the MAX function amongst this lot! The field which I need the max record from is MASTER18_HISTORY.mrGENERATION (as highlighted).

SELECT MASTER18.mrID, MASTER18_HISTORY.mrGENERATION, MASTER18_HISTORY.mrHISTORY,
SUBSTRING(SUBSTRING(MASTER18_HISTORY.mrHISTORY, 21, LEN(MASTER18_HISTORY.mrHISTORY)), 1, CHARINDEX(' ',
SUBSTRING(MASTER18_HISTORY.mrHISTORY, 21, LEN(MASTER18_HISTORY.mrHISTORY))) - 1) AS dlCLOSEDBY
FROM MASTER18 INNER JOIN
MASTER18_HISTORY ON MASTER18.mrID = MASTER18_HISTORY.mrID
WHERE (MASTER18_HISTORY.mrHISTORY LIKE '%closed%' and MASTER18.mrSTATUS = 'Closed')

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2008-04-09 : 11:42:34

Another way...


declare @t table (TicketID int, HistoryNO int, Description varchar(50))
insert @t
          select 8, 1, 'Description'
union all select 8, 2, 'Description'
union all select 8, 3, 'Description'
union all select 8, 4, 'Description'
union all select 9, 1, 'Description'
union all select 9, 2, 'Description'

select * from @t a where exists (select * from @t group by TicketID having TicketID = a.TicketID and max(HistoryNo) = a.HistoryNo)

/* results
TicketID    HistoryNO   Description
----------- ----------- --------------------------------------------------
8           4           Description
9           2           Description
*/

Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-09 : 12:15:02

[code]SELECT MASTER18.mrID, t.MaxGeneration, MASTER18_HISTORY.mrHISTORY,
SUBSTRING(SUBSTRING(MASTER18_HISTORY.mrHISTORY, 21, LEN(MASTER18_HISTORY.mrHISTORY)), 1, CHARINDEX(' ',
SUBSTRING(MASTER18_HISTORY.mrHISTORY, 21, LEN(MASTER18_HISTORY.mrHISTORY))) - 1) AS dlCLOSEDBY
FROM MASTER18
INNER JOIN
MASTER18_HISTORY ON MASTER18.mrID = MASTER18_HISTORY.mrID
INNER JOIN (SELECT mrID,MAX(mrGENERATION) AS MaxGeneration
FROM MASTER18_HISTORY
GROUP BY mrID)t
ON t.mrID=MASTER18_HISTORY.mrID
WHERE (MASTER18_HISTORY.mrHISTORY LIKE '%closed%' and MASTER18.mrSTATUS = 'Closed')[/code]

ramiatia
Starting Member

1 Post

Posted - 2008-04-09 : 20:26:39

You must have query + a sub query.
The main query will bring up all the rows and the sub query will look for the last row using order by desc + top 1 for each row you are looping through, so it would look like:

select
T1.ticketid,
(
select TOP 1 T1.HistoryNO from main_table_name T2 (nolock)
where T.ticketid = T2.ticketid order by HistoryNO DESC
)
from
main_table_name T1 (nolock)

Rami Atia
[url]http://www.amosoft.com[/url]

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-10 : 03:20:30

quote:
Originally posted by ramiatia

You must have query + a sub query.
The main query will bring up all the rows and the sub query will look for the last row using order by desc + top 1 for each row you are looping through, so it would look like:

select
T1.ticketid,
(
select TOP 1 T1.HistoryNO from main_table_name T2 (nolock)
where T.ticketid = T2.ticketid order by HistoryNO DESC
)
from
main_table_name T1 (nolock)

Rami Atia
[url]http://www.amosoft.com[/url]

wow

Em

domo
Starting Member

23 Posts

Posted - 2008-04-10 : 03:48:31

quote:
Originally posted by ramiatia

You must have query + a sub query.
The main query will bring up all the rows and the sub query will look for the last row using order by desc + top 1 for each row you are looping through, so it would look like:

select
T1.ticketid,
(
select TOP 1 T1.HistoryNO from main_table_name T2 (nolock)
where T.ticketid = T2.ticketid order by HistoryNO DESC
)
from
main_table_name T1 (nolock)

Rami Atia
[url]http://www.amosoft.com[/url]

I receive the following error message:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "T.mrID" could not be bound.

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-10 : 03:52:39

quote:
Originally posted by domo

quote:
Originally posted by ramiatia

You must have query + a sub query.
The main query will bring up all the rows and the sub query will look for the last row using order by desc + top 1 for each row you are looping through, so it would look like:

select
T1.ticketid,
(
select TOP 1 T1.HistoryNO from main_table_name T2 (nolock)
where T.ticketid = T2.ticketid order by HistoryNO DESC
)
from
main_table_name T1 (nolock)

Rami Atia
[url]http://www.amosoft.com[/url]

I receive the following error message:
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "T.mrID" could not be bound.

really.... don't use that query. did you try visakh's?

Em

domo
Starting Member

23 Posts

Posted - 2008-04-10 : 04:07:47

quote:
Originally posted by elancaster
really.... don't use that query. did you try visakh's?

Em

Yeah I did try visakh's. It's almost there except it outputs a line for each instance of dlCLOSEDBY or mrID (not sure which) as below, as opposed to only outputting the single record with the highest mrGENERATION (shown in the output as MaxGeneration).

mrID, MaxGeneration, dlCLOSEDBY
8 20 2008-01-18 16:21:56 DL5404 Closed Changed status...
8 20 2008-01-18 16:21:56 DL5404 Closed WRAPPED17162156...
8 20 2008-01-18 16:21:56 DL5404 Closed ED1716215635431...
8 20 2008-01-18 16:21:56 DL5404 Closed WRAPPED17162156...
10 21 2008-01-18 16:23:45 DL5404 Closed Changed status...
10 21 2008-01-18 16:23:45 DL5404 Closed WRAPPED1716234...
10 21 2008-01-18 16:23:45 DL5404 Closed WRAPPED1716234...
10 21 2008-01-18 16:23:45 DL5404 Closed WRAPPED1716234...

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-04-10 : 04:19:49

ah, just spotted it...

you need to join on the date as well...

ON t.mrID=MASTER18_HISTORY.mrID and t.maxGeneration= MASTER18_HISTORY.mrGeneration

Em

domo
Starting Member

23 Posts

Posted - 2008-04-10 : 04:36:12

quote:
Originally posted by elancaster

ah, just spotted it...

you need to join on the date as well...

ON t.mrID=MASTER18_HISTORY.mrID and t.maxGeneration= MASTER18_HISTORY.mrGeneration

Em

That appears to have done the job - excellent stuff Em. Thanks very much for your help everyone.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources