Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
| Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 25th, 2005, 07:37 AM
Registered User
 
Join Date: Mar 2005
Location: , , .
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Query - picking latest record and group by

Hi,

Can anybody help, I have the following query below.

SELECT jobid, cssjob, busresid, ccd, rag, pt1b, pt1c, rt1b, rt1c, received, waiterstatus, cssdataid
FROM dbo.cssdata
WHERE (waiterstatus = 0)

Whic produces the following results, (this is a snapshot)

jobid Cssjob busres ccd rag pt1b pt1c rt1c cssdataid

118300 MC R 01/03/2005 1 0 1 11456
118300 AX R 01/03/2005 1 1 1 11452
118300 CE B 01/03/2005 2 1 1 11451
118900 FR A 01/03/2005 3 1 1 11500
118900 CD A 01/03/2005 2 1 1 11499
118900 MS A 01/03/2005 1 1 1 11478
118900 LM A 01/03/2005 1 1 1 11473

I need help in the following, I need for each jobid, to select the rcord with the hihest cssdataid, so for 118300, I need to select the record which contains cssdataid = 11456, I will always need to select the record with latest (highest) cssdataid number. Also I want to be able to to sum up pt1b, pt1c, rt1c and group by jobid. The result I am looking for is below

jobid Cssjob busres ccd rag pt1b pt1c rt1c cssdataid

118300 MC R 01/03/2005 4 2 3 11456
118900 FR A 01/03/2005 7 4 4 11500

How can I do this

Thanks

Mark


 
Old March 25th, 2005, 02:06 PM
Friend of Wrox
 
Join Date: Jan 2004
Location: , , USA.
Posts: 303
Thanks: 0
Thanked 0 Times in 0 Posts
Default

First of all normalize you table design


 
Old April 6th, 2005, 03:54 AM
Authorized User
 
Join Date: Feb 2005
Location: Pune, Maharashtra, India.
Posts: 30
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Let's try this

SELECT JobId, Cssjob, BusRes, ccd, rag, Sumpt1b, Sumpt1c, Sumrt1c, MaxCssDataId
FROM dbo.cssdata Main,
    (SELECT JobId MaxJobId,MAX(CssDataId) MaxCssDataId
     FROM dbo.cssdata
     WHERE waiterstatus = 0
     GROUP BY JobId) AS MaxCDI,
    (SELECT JobId SumJobId,SUM(pt1b) Sumpt1b,SUM(pt1c) Sumpt1c,SUM(pt1c) Sumrt1c,
     FROM dbo.cssdata
     WHERE waiterstatus = 0
     GROUP BY JobId) AS SumCDI
WHERE WaiterStatus = 0
AND Main.JobId=MaxCDI.MaxJobId AND Main.CssDataId=MaxCDI.MaxCssDataId
AND Main.JobId=SumCDI.SumJobId

I have used two sub queries MaxCDI and SumCDI.

Cheers,
Pooja Falor




Similar Threads
Thread Thread Starter Forum Replies Last Post
Getting distinct and latest record elygp SQL Language 4 May 4th, 2008 10:49 PM
One Record From Each Group - Query rstelma SQL Server 2000 7 January 3rd, 2008 12:08 AM
SQL query using "Group By" - please help BananaJim SQL Language 2 February 26th, 2007 10:23 AM
SQL query retrieving last record and group by snowy SQL Language 2 December 13th, 2006 01:59 PM
Retreive latest and greatest record alexvgs Oracle 3 April 25th, 2005 09:57 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.