Selecting latest version. - 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

General SQL Server Forums

New to SQL Server Programming

Selecting latest version.

Author

Topic

lwarunek
Starting Member

22 Posts

Posted - 2008-02-11 : 09:19:40

Hi everybody!

I am developing realese system. It contains application, application releases and changes that have been made in a particular release.

What i need to do is: I need to retrieve the latest version for a particular application and the latest change so as a result I would see in a table:
application, latest version and latest change for the latest version.

I tried to querry that using Max function but it does not seem to work as I get e.g. 2 rows when there are 2 records for the same version.

Could anyone help me?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 09:22:30

How about some table DDL so we might help you?
Consider your question above similar to you calling a doctor over the phone and telling him
"I have pain. Give advice to not have pain anymore".

Aka see this blog post http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

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

lwarunek
Starting Member

22 Posts

Posted - 2008-02-11 : 09:27:36

This are the tables:

CREATE TABLE [dbo].[tbl_applications] (
[applicationID] [int] IDENTITY (1, 1) NOT NULL ,
[applicationName] [varchar] (250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[storeServer] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dbName] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateAdded] [datetime] NULL
)

CREATE TABLE [dbo].[tbl_changes] (
[changeID] [int] IDENTITY (1, 1) NOT NULL ,
[versionID] [int] NULL ,
[change] [varchar] (8000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[dateadded] [datetime] NULL ,
[inUse] [tinyint] NULL ,
[achieved] [tinyint] NULL
)

CREATE TABLE [dbo].[tbl_versions] (
[versionID] [int] IDENTITY (1, 1) NOT NULL ,
[version] [float] NULL ,
[dateadded] [datetime] NULL ,
[applicationID] [int] NULL ,
[live] [tinyint] NULL
)
and the querry

SELECT

'v'+CAST(V.version as varchar) as version,
V.dateAdded,
CAST(C.change as varchar(100)) + '...' as change,
V.versionID,
C.changeID,
A.applicationName,
A.applicationID,
A.storeServer

FROM tbl_applications A
INNER JOIN tbl_versions V ON
V.applicationID = A.applicationID
INNER JOIN tbl_changes C ON
V.versionID = C.versionID

WHERE

V.version = (SELECT Max(version) from tbl_versions Vt where Vt.live=1)
and C.dateAdded = (SELECT Max(dateAdded) from tbl_changes Ct where Ct.inUse=1 and Ct.achieved=1)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 09:30:56

Are you using SQL Server 2000 or SQL Server 2005?

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

lwarunek
Starting Member

22 Posts

Posted - 2008-02-11 : 09:31:56

2000

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 09:34:02

[code]SELECT
'v'+CAST(V.version as varchar) as version,
V.dateAdded,
CAST(C.change as varchar(100)) + '...' as change,
V.versionID,
C.changeID,
A.applicationName,
A.applicationID,
A.storeServer

FROM tbl_applications A
INNER JOIN tbl_versions V ON
V.applicationID = A.applicationID
INNER JOIN (SELECT A1.applicationID,MAX(VersionID) AS MaxVersion
FROM tbl_applications A1
INNER JOIN tbl_versions V1
ON V1.applicationID=A1.applicationID
GROUP BY A1.applicationID)MaxVer
ON A.applicationID=MaxVer.applicationID
AND V.VersionID=MaxVer.MaxVersion
INNER JOIN tbl_changes C ON
MaxVer.MaxVersion = C.versionID[/code]

lwarunek
Starting Member

22 Posts

Posted - 2008-02-11 : 09:36:46

Ok. it retrieves the latest version but 2 rows coz there are two changes for the latest version. So it needs to retrieve the latest version and the latest change for that version so I have only 1 row.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 09:39:23

If you are serious and want fast help, you should follow all advice in the link provided above.
Where are the sample data?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-02-11 : 09:42:52

quote:
Originally posted by lwarunek

Ok. it retrieves the latest version but 2 rows coz there are two changes for the latest version. So it needs to retrieve the latest version and the latest change for that version so I have only 1 row.

SELECT 
'v'+CAST(V.version as varchar) as version,
V.dateAdded,
CAST(C.change as varchar(100)) + '...' as change,
V.versionID,
C.changeID,
A.applicationName,
A.applicationID,
A.storeServer

FROM tbl_applications A
INNER JOIN tbl_versions V ON
V.applicationID = A.applicationID
INNER JOIN (SELECT  A1.applicationID,MAX(VersionID) AS MaxVersion
            FROM tbl_applications A1
            INNER JOIN tbl_versions V1
            ON V1.applicationID=A1.applicationID
            GROUP BY A1.applicationID)MaxVer
ON A.applicationID=MaxVer.applicationID
AND V.VersionID=MaxVer.MaxVersion
INNER JOIN tbl_changes C ON
MaxVer.MaxVersion = C.versionID
INNER JOIN (SELECT  C1.versionID,MAX(ChangeID) AS MaxChange
            FROM tbl_changes C1
            GROUP BY C1.versionID)MaxChg
ON MaxChg.MaxChange=C.ChangeID
AND MaxChg.versionID=C.versionID

lwarunek
Starting Member

22 Posts

Posted - 2008-02-11 : 09:46:32

Cheers for that!
This one works! Cool.

Good stuff.

lwarunek
Starting Member

22 Posts

Posted - 2008-02-11 : 09:49:17

FROM tbl_applications A
INNER JOIN tbl_versions V ON
V.applicationID = A.applicationID
INNER JOIN (SELECT A1.applicationID,MAX(VersionID) AS MaxVersion
FROM tbl_applications A1
INNER JOIN tbl_versions V1
ON V1.applicationID=A1.applicationID
GROUP BY A1.applicationID)MaxVerON A.applicationID=MaxVer.applicationID
AND V.VersionID=MaxVer.MaxVersion
INNER JOIN tbl_changes C ON
MaxVer.MaxVersion = C.versionID
INNER JOIN (SELECT C1.versionID,MAX(ChangeID) AS MaxChange
FROM tbl_changes C1
GROUP BY C1.versionID)MaxChg
ON MaxChg.MaxChange=C.ChangeID
AND MaxChg.versionID=C.versionID

Out of interest, could u tell me what kind of joint is that? (the one that I have put bold)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 09:51:41

[code]-- Prepare staging table
DECLARE @Stage TABLE
(
RowID INT IDENTITY(1, 1),
ApplicationID INT,
ApplicationName VARCHAR(250),
StoreServer VARCHAR(80),
DbName VARCHAR(80),
appDateAdded DATETIME,
VersionID INT,
Version FLOAT,
verDateAdded DATETIME,
ChangeID INT,
Change VARCHAR(8000),
chgDateAdded DATETIME
)

-- Populate staging table
INSERT @Stage
(
ApplicationID,
ApplicationName,
StoreServer,
DbName,
appDateAdded,
VersionID,
Version,
verDateAdded,
ChangeID,
Change,
chgDateAdded
)
SELECT a.ApplicationID,
a.ApplicationName,
a.StoreServer,
a.DbName,
a.appDateAdded
v.VersionID,
v.Version,
v.verDateAdded,
c.ChangeID,
c.Change,
c.DateAdded
FROM tbl_Applications AS a
INNER JOIN tbl_Versions AS v ON v.ApplicationID = a.ApplicationID
INNER JOIN tbl_Changes AS c ON c.VersionID = v.VersionID
WHERE v.Live = 1
AND c.InUse = 1
AND c.Achieved = 1
ORDER BY a.ApplicationName,
v.Version DESC

-- Show the expected output
SELECT s.ApplicationID,
s.ApplicationName,
s.StoreServer,
s.DbName,
s.appDateAdded,
s.VersionID,
s.Version,
s.verDateAdded,
s.ChangeID,
s.Change,
s.chgDateAdded
FROM @Stage AS s
INNER JOIN (
SELECT ApplicationName,
MIN(RowID) AS RowID
FROM @Stage
GROUP BY ApplicationName
) AS x ON x.ApplicationName = s.ApplicationName
WHERE s.RowID = x.RowID
ORDER BY s.ApplicationName[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-02-11 : 09:52:40