Select Most Recent 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

General SQL Server Forums

New to SQL Server Programming

Select Most Recent Record

Author

Topic

bielen
Yak Posting Veteran

97 Posts

Posted - 2008-11-13 : 14:19:55

I have two tables:

RELEASE_JOBS
ReleaseID release_number release_date status
1 1 11/12/08 Complete
2 1 11/19/08 Scheduled
3 2 11/13/08 In-Progres
4 3 11/15/08 Scheduled
5 3 11/18/08 Scheduled
4 4 11/11/0 Completed

RELEASES
ReleaseID release_number
1 1.0
2 2.0
3 3.0
4 4.0

What I want to do is display the most recent date with the release number and current status:

Release Number Release Date Time Status
1.0 11/19/08 1:00pm Scheduled
2.0 11/13/08 12:00pm In-Progress
3.0 11/18/08 9:00am Scheduled
4.0 11/11/08 8:00am Completed

I've tried different queries with max date and group by, but can't get the syntax down to eliminate the older records.

Any info would be appreciated.

bielen
Yak Posting Veteran

97 Posts

Posted - 2008-11-13 : 14:38:07

As a follow-up to my original note, I have the following query:

SELECT MAX(dbo.RELEASE_JOBS.release_date) AS release_date, dbo.RELEASES.releaseDesc
FROM dbo.RELEASES INNER JOIN dbo.RELEASE_JOBS ON dbo.RELEASE_JOBS.product_release = dbo.RELEASES.releaseID
GROUP BY dbo.RELEASES.releaseDesc
ORDER BY dbo.RELEASE_JOBS.release_date DESC

This provides the output, but as soon as I add the time or status, the duplicates display.

bielen
Yak Posting Veteran

97 Posts

Posted - 2008-11-13 : 16:27:35

I have the script somewhat work as:

SELECT t1.*, v.maxDate
FROM dbo.RELEASE_RELEASES t1 INNER JOIN
(SELECT t2.product_release, MAX(t2.RELEASE_date) AS maxDate
FROM RELEASE_JOBS t2
GROUP BY t2.product_release, RELEASE_start_time) v ON v.product_release = t1.releaseID
ORDER BY v.maxDate DESC

The script displays the to results until I add items to the group by. How can I add a field such as status and still keep the top record for each?

Thank you for any info you can provide.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-13 : 23:05:12

[code]SELECT r.Release_number,rj.release_date,rj.status
FROM RElease_Jobs rj
JOIN Releases r
on r.ReleaseID=rj.Release_Number
JOIN (SELECT Release_number,MAX(release_date) as maxdate
FROM RELEASE_JOBS
GROUP BY Release_number)rj1
on rj1.Release_number=rj.Release_number
and rj.release_date=rj1.maxdate[/code]

raky
Aged Yak Warrior

767 Posts

Posted - 2008-11-14 : 03:37:00

--Select Most Recent Record

CREATE TABLE #RELEASE_JOBS (ReleaseID INT, release_number INT ,release_date DATETIME, status VARCHAR(64))
INSERT INTO #RELEASE_JOBS
SELECT 1, 1, '11/12/08', 'Complete' UNION ALL
SELECT 2, 1 ,'11/19/08', 'Scheduled' UNION ALL
SELECT 3, 2, '11/13/08', 'In-Progres' UNION ALL
SELECT 4, 3, '11/15/08', 'Scheduled' UNION ALL
SELECT 5, 3, '11/18/08', 'Scheduled' UNION ALL
SELECT 4, 4, '11/11/08', 'Completed'

SELECT * FROM #RELEASE_JOBS

CREATE TABLE #RELEASES (releaseid INT, release_number NUMERIC(18,1))
INSERT INTO #RELEASES
(ReleaseID, release_number)
SELECT 1 ,1.0 UNION ALL
SELECT 2 ,2.0 UNION ALL
SELECT 3, 3.0 UNION ALL
SELECT 4, 4.0

SELECT * FROM #RELEASES

SELECT
t.release_number,t.release_date,rjj.status
FROM
#RELEASE_JOBS AS rjj
RIGHT JOIN
(SELECT
r.release_number,MAX(rj.release_date) AS release_date
FROM
#RELEASE_JOBS AS rj
INNER JOIN
#RELEASES AS r ON r.releaseid = rj.release_number
GROUP BY r.release_number) AS t ON t.release_date = rjj.release_date

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 07:24:32

quote:
Originally posted by raky

--Select Most Recent Record

CREATE TABLE #RELEASE_JOBS (ReleaseID INT, release_number INT ,release_date DATETIME, status VARCHAR(64))
INSERT INTO #RELEASE_JOBS
SELECT 1, 1, '11/12/08', 'Complete' UNION ALL
SELECT 2, 1 ,'11/19/08', 'Scheduled' UNION ALL
SELECT 3, 2, '11/13/08', 'In-Progres' UNION ALL
SELECT 4, 3, '11/15/08', 'Scheduled' UNION ALL
SELECT 5, 3, '11/18/08', 'Scheduled' UNION ALL
SELECT 4, 4, '11/11/08', 'Completed'

SELECT * FROM #RELEASE_JOBS

CREATE TABLE #RELEASES (releaseid INT, release_number NUMERIC(18,1))
INSERT INTO #RELEASES
(ReleaseID, release_number)
SELECT 1 ,1.0 UNION ALL
SELECT 2 ,2.0 UNION ALL
SELECT 3, 3.0 UNION ALL
SELECT 4, 4.0

SELECT * FROM #RELEASES

SELECT
t.release_number,t.release_date,rjj.status
FROM
#RELEASE_JOBS AS rjj
RIGHT JOIN
(SELECT
r.release_number,MAX(rj.release_date) AS release_date
FROM
#RELEASE_JOBS AS rj
INNER JOIN
#RELEASES AS r ON r.releaseid = rj.release_number
GROUP BY r.release_number) AS t ON t.release_date = rjj.release_date

whats the purpose of right join?

bielen
Yak Posting Veteran

97 Posts

Posted - 2008-11-14 : 10:50:57

I have updated the script but still receive duplicates. Any idea on what I am doing incorrectly?

SELECT r.releaseDesc, rj.release_date, rj.release_status
FROM dbo.RELEASE_JOBS rj INNER JOIN
dbo.RELEASES r ON r.releaseID = rj.product_release INNER JOIN
(SELECT release_number, MAX(release_date) AS maxdate
FROM RELEASE_JOBS
GROUP BY RELEASE_number) rj1 ON rj1.release_number = rj.release_number AND rj.release_date = rj1.maxdate

OUTPUT FROM QUERY
releaseDesc release_date release_status
5.0 11/12/2008 2
1.0 11/20/2008 1
1.0 11/13/2008 1
4.0 11/21/2008 1
4.0 11/6/2008 2
2.0 11/21/2008 1
2.0 11/14/2008 1
3.0 11/21/2008 1
3.0 11/14/2008 1

RELEASE_JOBS
product_release release_date release_status
4 11/13/2008 1
6 11/21/2008 1
7 11/21/2008 1
3 11/12/2008 2
3 11/19/2008 1
5 11/6/2008 2
7 11/14/2008 1
5 11/21/2008 1
4 11/20/2008 1
6 11/14/2008 1

RELEASES
releaseID releaseDesc
3 5.0
4 1.0
5 4.0
6 2.0
7 3.0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 10:59:01

where's release_number column? whats the column by which two tables are related?

bielen
Yak Posting Veteran

97 Posts

Posted - 2008-11-14 : 13:56:39

The release_number column is int he RELEASE_JOBS table. It is just a thre digital integer. I kept it out to keep things simple, but left it it in the query.

The columns are related by product_release in the RELEASE_JOBS table and releaseID in the RELEASES table.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-11-14 : 23:36:23

then it should be this

SELECT r.releaseDesc, rj.release_date, rj.release_status
FROM dbo.RELEASE_JOBS rj INNER JOIN
dbo.RELEASES r ON r.releaseID = rj.product_release INNER JOIN
(SELECT product_release, MAX(release_date) AS maxdate
FROM RELEASE_JOBS
GROUP BY product_release) rj1 ON rj1.product_release = rj.product_release AND rj.release_date = rj1.maxdate

bielen
Yak Posting Veteran

97 Posts

Posted - 2008-11-17 : 13:42:46

Visakh16. Thank you. Everything worked out. Your sharing of knowledge is extremely helpful to the learning process.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources