Selecting records according to max value

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 2005 Forums

Transact-SQL (2005)

Selecting records according to max value

Author

Topic

mattlemon
Starting Member

4 Posts

Posted - 2007-11-12 : 10:35:30

Hi, I have 2 related tables, one holds names and stuff and the other holds a kind of audit trail related to the names in the first table.

What I want to select is everything from table one and only the most recent audit event from the 2nd table. Can I do it ? No. The audit (event) id in numeric and each user starts at 1 so the event ID is specific to them.

Current TSQL is :

SELECT DISTINCT
TOP (100) PERCENT dbo.WFTA.txtTitle AS Title, dbo.WFTA.txtForename AS forename, dbo.WFTA.txtLastName, dbo.WFTA.chkEaster2008, dbo.WFTA.chkEaster2009,
dbo.WFTA.chkJan2008, dbo.WFTA.chkJan2009, dbo.WFTA.chkSept2008, dbo.WFTA.chkSept2009, dbo.eEvent.eToStage, MAX(dbo.eEvent.eEventID) AS EventID
FROM dbo.WFTA LEFT OUTER JOIN
dbo.eEvent ON dbo.WFTA.EFOLDERID = dbo.eEvent.eFolderID
WHERE (dbo.eEvent.eMapName = 'WFTA')
GROUP BY dbo.WFTA.txtLastName, dbo.WFTA.chkEaster2008, dbo.WFTA.chkEaster2009, dbo.WFTA.chkJan2008, dbo.WFTA.chkJan2009, dbo.WFTA.chkSept2008,
dbo.WFTA.chkSept2009, dbo.eEvent.eToStage, dbo.WFTA.txtTitle, dbo.WFTA.txtForename

It's giving me all the records from table 1 and any matching from 2.

Please help, it's driving me mad !!

Cheers,

Matt

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 11:21:42

Do an inner join to the max folder ID as a derived table?



FROM dbo.WFTA 
LEFT JOIN dbo.eEvent ON dbo.WFTA.EFOLDERID = 
on dbo.eEvent ON dbo.WFTA.EFOLDERID = dbo.eEvent.eFolderID

INNER JOIN (Select Max(dbo.eEvent.eFolderID) as MaxID  
                   FROM  dbo.eEvent) MID
 dbo.eEvent.eFolderID = MID.MaxID

Poor planning on your part does not constitute an emergency on my part.

mattlemon
Starting Member

4 Posts

Posted - 2007-11-12 : 15:46:45

I've been playing around with that and whilst I'm sure it will work I can't get it to. SO, what I've decided to do is to write an SP to select the data from that I want into a temp table then join that with the first table and use that.

select dbo.eEvent.eFolderID, dbo.eEvent.eToStage, dbo.eEvent.eEventID from eEvent where dbo.eEvent.eMapName = 'WFTA'

What I need to change in the query above is to return the maximum eEventID for each of the eFolderID values. There may be 10 records with the same eFolderID and it's eEventID will go from 0 to 10. So I need something that will give me the results something like :

eFolderID eToStage eEventID
100 a 7
101 b 3
103 a 7

etc

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 16:01:27

You would want to have your subquery to group by and include the folder ID then, something more like this.

If you could post the DDL with sample values to query from it would be easier.


 	
INNER JOIN (Select Max(eEventID) as MaxID,eFolderID   
                   FROM  dbo.eEvent
                   Group by eFolderID) MID
 dbo.eEvent.eEventID = MID.MaxID  and dbo.eEvent.eFolderID = MID.eFolderID

this query you posted:


select dbo.eEvent.eFolderID, dbo.eEvent.eToStage, dbo.eEvent.eEventID from eEvent where dbo.eEvent.eMapName = 'WFTA'

Would be :


select dbo.eEvent.eFolderID, dbo.eEvent.eToStage,Max(dbo.eEvent.eEventID) as MaxEventID
from eEvent 
where dbo.eEvent.eMapName = 'WFTA'
Group by dbo.eEvent.eFolderID, dbo.eEvent.eToStage

To get the max event id

Poor planning on your part does not constitute an emergency on my part.

mattlemon
Starting Member

4 Posts

Posted - 2007-11-12 : 16:10:58

Thanks for that, I'll have a play with that in the mean time the DDL for the two tables is :

USE [Metastorm]
GO
/****** Object: Table [dbo].[eEvent] Script Date: 11/12/2007 21:04:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eEvent](
[eFolderID] [nvarchar](31) NOT NULL,
[eEventID] [int] NOT NULL,
[eVersion] [int] NULL,
[ePriority] [smallint] NULL,
[eEventTime] [datetime] NULL,
[eEntryTime] [datetime] NULL,
[eDeadline] [datetime] NULL,
[eMapName] [nvarchar](31) NULL,
[eFromStage] [nvarchar](31) NULL,
[eActionName] [nvarchar](31) NULL,
[eToStage] [nvarchar](31) NULL,
[eUserName] [nvarchar](100) NULL,
[eFlagName] [nvarchar](63) NULL,
[eFlagFolder] [nvarchar](31) NULL,
[eAlertMessage] [nvarchar](250) NULL,
[eNotes] [ntext] NULL,
CONSTRAINT [ePKU_eEvent] PRIMARY KEY CLUSTERED
(
[eFolderID] ASC,
[eEventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

USE [Metastorm]
GO
/****** Object: Table [dbo].[eEvent] Script Date: 11/12/2007 21:04:55 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[eEvent](
[eFolderID] [nvarchar](31) NOT NULL,
[eEventID] [int] NOT NULL,
[eVersion] [int] NULL,
[ePriority] [smallint] NULL,
[eEventTime] [datetime] NULL,
[eEntryTime] [datetime] NULL,
[eDeadline] [datetime] NULL,
[eMapName] [nvarchar](31) NULL,
[eFromStage] [nvarchar](31) NULL,
[eActionName] [nvarchar](31) NULL,
[eToStage] [nvarchar](31) NULL,
[eUserName] [nvarchar](100) NULL,
[eFlagName] [nvarchar](63) NULL,
[eFlagFolder] [nvarchar](31) NULL,
[eAlertMessage] [nvarchar](250) NULL,
[eNotes] [ntext] NULL,
CONSTRAINT [ePKU_eEvent] PRIMARY KEY CLUSTERED
(
[eFolderID] ASC,
[eEventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

The only data that I'm interested is :

SELECT
dbo.WFTA.txtTitle AS Title, dbo.WFTA.txtForename AS forename, dbo.WFTA.txtLastName, dbo.WFTA.chkEaster2008, dbo.WFTA.chkEaster2009,
dbo.WFTA.chkJan2008, dbo.WFTA.chkJan2009, dbo.WFTA.chkSept2008, dbo.WFTA.chkSept2009, dbo.eEvent.eToStage, MAX(dbo.eEvent.eEventID) AS EventID
FROM dbo.WFTA LEFT OUTER JOIN
dbo.eEvent ON dbo.WFTA.EFOLDERID = dbo.eEvent.eFolderID
WHERE (dbo.eEvent.eMapName = 'WFTA')

an example of the results from above is :

Ms Elizabeth Anne Alexander 0 0 0 0 0 -1 Consideration List 2
Ms Elizabeth Anne Alexander 0 0 0 0 0 -1 Start 2009 1
Ms Elizabeth Anne Alexander 0 0 0 0 0 -1 Trainee Team 0
Mr Robert Allen 0 0 0 0 0 -1 Application 11
Mr Robert Allen 0 0 0 0 0 -1 Final Outcome NO list 13
Mr Robert Allen 0 0 0 0 0 -1 Trainee Team 12
Ms Elizabeth Armstrong 0 0 0 0 0 0 Application 8
Ms Elizabeth Armstrong 0 0 0 0 0 0 Bin 9
Ms Olga Ashe 0 0 0 0 0 -1 Application 107
Ms Olga Ashe 0 0 0 0 0 -1 Consideration List 110
Ms Olga Ashe 0 0 0 0 0 -1 Start 2009 109
Ms Olga Ashe 0 0 0 0 0 -1 Trainee Team 108
Mr Femi Ashiyanbi 0 0 0 0 -1 0 Application 20
Mr Femi Ashiyanbi 0 0 0 0 -1 0 Consideration List 23
Mr Femi Ashiyanbi 0 0 0 0 -1 0 Start 2009 22
Mr Femi Ashiyanbi 0 0 0 0 -1 0 Trainee Team 21
Ms Ursula Atueyi Nwosu 0 0 0 -1 0 0 Application 24
Ms Ursula Atueyi Nwosu 0 0 0 -1 0 0 Consideration List 28
Ms Ursula Atueyi Nwosu 0 0 0 -1 0 0 Start 2009 27
Ms Ursula Atueyi Nwosu 0 0 0 -1 0 0 Trainee Team 25

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-11-12 : 16:32:48

So I am guessing you need the results to look like:


Ms Elizabeth Anne Alexander 0 0 0 0 0 -1 Consideration List 2
Mr Robert Allen 0 0 0 0 0 -1 Final Outcome NO list 13
Ms Elizabeth Armstrong 0 0 0 0 0 0 Bin 9
Ms Olga Ashe 0 0 0 0 0 -1 Consideration List 110
Mr Femi Ashiyanbi 0 0 0 0 -1 0 Consideration List 23
Ms Ursula Atueyi Nwosu 0 0 0 -1 0 0 Consideration List 28

In which case the bolder part below with an inner join on the subquery (which returns the folder and max ID) might do it.


SELECT
dbo.WFTA.txtTitle AS Title, dbo.WFTA.txtForename AS forename, 
dbo.WFTA.txtLastName, 
dbo.WFTA.chkEaster2008, 
dbo.WFTA.chkEaster2009,
dbo.WFTA.chkJan2008, dbo.WFTA.chkJan2009, 
dbo.WFTA.chkSept2008, dbo.WFTA.chkSept2009,
dbo.eEvent.eToStage,dbo.eEvent.eEventID
FROM dbo.WFTA LEFT OUTER JOIN  dbo.eEvent 
        ON dbo.WFTA.EFOLDERID = dbo.eEvent.eFolderID
        
            INNER JOIN ( Select Max(EventID) as ID,eFolderID
                           FROM dbo.eEvent
                           Group by eFolderID) maxevent
             on dbo.eEvent.eEventID = maxevent.ID and dbo.eEvent.eFolderID = maxevent.eFolderID
WHERE (dbo.eEvent.eMapName = 'WFTA')

Poor planning on your part does not constitute an emergency on my part.

mattlemon
Starting Member

4 Posts

Posted - 2007-11-13 : 05:48:17

Perfect, works a treat !

Looks like I need to brush up on my joins

Thanks,

Matt

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-11-13 : 06:29:43

Try this SQL Server 2005 approach too

SELECT	Title,
	forename,
	txtLastName,
	chkEaster2008,
	chkEaster2009,
	chkJan2008,
	chkJan2009,
	chkSept2008,
	chkSept2009,
	eToStage,
	eEventID
FROM	(	
		SELECT		a.txtTitle AS Title,
				a.txtForename AS forename,
				a.txtLastName,
				a.chkEaster2008,
				a.chkEaster2009,
				a.chkJan2008,
				a.chkJan2009,
				a.chkSept2008,
				a.chkSept2009,
				e.eToStage,
				e.eEventID,
				ROW_NUMBER() OVER (PARTITION BY dbo.eEvent.eFolderID ORDER BY dbo.eEvent.eEventID DESC) AS RecID
		FROM		dbo.WFTA AS a
		LEFT JOIN	dbo.eEvent AS e ON e.eFolderID = a.EFOLDERID
		WHERE		e.eMapName = 'WFTA'
	) AS d
WHERE	RecID = 1

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources