Pull Most Recent Date from a group of records

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)

Pull Most Recent Date from a group of records

Author

Topic

Drathnar
Starting Member

8 Posts

Posted - 2008-12-04 : 17:37:03

I have a query that pulls the following…

All fields are the same with the exception of the last field and the upsellprice field what my intent is to limit this to only display the most recent entry for any given item rather than display the history as well.. However everything I have tried has not worked.. the closest i feel i have gotten is the query below

Item1 7 DC Item1# E 01 156 0.00 DP 0.00 12/4/2008 7:50:17 AM NULL NULL 12.95 34.95 12/4/2008 12:00:00 AM

Item1 7 DC Item1# E 01 156 0.00 DP 0.00 12/4/2008 7:50:17 AM NULL NULL 16.95 34.95 12/2/2008 12:00:00 AM

Item1 7 DC Item1# E 01 156 0.00 DP 0.00 12/4/2008 7:50:17 AM NULL NULL 20.95 34.95 11/27/2008 12:00:00 AM

SELECT dbo.tbl_PIC182.ItemDescription, dbo.tbl_PIC180.Company, dbo.tbl_PIC180.Catalog, dbo.tbl_PIC180.ItemNo, dbo.tbl_PIC180.Type, dbo.tbl_PIC180.[Group], dbo.tbl_PIC180.Location, dbo.tbl_PIC180.Page, dbo.tbl_PIC180.Commission, dbo.tbl_PIC180.UpsellPrefix, dbo.tbl_PIC180.UnitPrice2, dbo.tbl_PIC180.DateAdded, dbo.tbl_PIC180.DateModified, dbo.tbl_PIC180.ModifiedBy, dbo.tbl_PIC182.UpsellPrice, dbo.tbl_PIC182.Price AS UnitPrice, MAX Date(dbo.tbl_PIC182.Date) AS Expr1

FROM
dbo.tbl_PIC180 INNER JOIN
dbo.tbl_PIC182
ON
dbo.tbl_PIC180.ItemNo = dbo.tbl_PIC182.ItemNo

WHERE ('Expr1' IN
(SELECT MAX DATE('Expr1') AS Expr2
FROM dbo.tbl_PIC180 AS tbl_PIC180_1 INNER JOIN
dbo.tbl_PIC182 AS tbl_PIC182_1
ON
tbl_PIC180_1.ItemNo = tbl_PIC182_1.ItemNo
GROUP BY tbl_PIC182_1.ItemNo, tbl_PIC180_1.Company, tbl_PIC180_1.Catalog))

GROUP BY dbo.tbl_PIC182.ItemDescription, dbo.tbl_PIC180.Company, dbo.tbl_PIC180.Catalog, dbo.tbl_PIC180.ItemNo, dbo.tbl_PIC180.Type,
dbo.tbl_PIC180.[Group], dbo.tbl_PIC180.Location, dbo.tbl_PIC180.Page, dbo.tbl_PIC180.Commission, dbo.tbl_PIC180.UpsellPrefix, dbo.tbl_PIC180.UnitPrice2, dbo.tbl_PIC180.DateAdded, dbo.tbl_PIC180.DateModified, dbo.tbl_PIC180.ModifiedBy, dbo.tbl_PIC182.UpsellPrice, dbo.tbl_PIC182.Price

chrianth
Yak Posting Veteran

50 Posts

Posted - 2008-12-05 : 01:06:23

You can try the query below...hope the output is what you're expecting. :)

SELECT dbo.tbl_PIC182.ItemDescription,
dbo.tbl_PIC180.Company,
dbo.tbl_PIC180.Catalog,
dbo.tbl_PIC180.ItemNo,
dbo.tbl_PIC180.Type,
dbo.tbl_PIC180.[Group],
dbo.tbl_PIC180.Location,
dbo.tbl_PIC180.Page,
dbo.tbl_PIC180.Commission,
dbo.tbl_PIC180.UpsellPrefix,
dbo.tbl_PIC180.UnitPrice2,
dbo.tbl_PIC180.DateAdded,
dbo.tbl_PIC180.DateModified,
dbo.tbl_PIC180.ModifiedBy,
dbo.tbl_PIC182.UpsellPrice,
dbo.tbl_PIC182.Price AS UnitPrice,
dbo.tbl_PIC182.Date AS Date

FROM dbo.tbl_PIC180 I
INNER JOIN dbo.tbl_PIC182
ON dbo.tbl_PIC180.ItemNo = dbo.tbl_PIC182.ItemNo
join (SELECT MAX(tbl_PIC182_1.Date) AS Date,
tbl_PIC182_1.ItemNo AS ItemNo,
tbl_PIC180_1.Company AS Company,
tbl_PIC180_1.[Catalog] as [Catalog]
FROM dbo.tbl_PIC180 AS tbl_PIC180_1 INNER JOIN
dbo.tbl_PIC182 AS tbl_PIC182_1
ON tbl_PIC180_1.ItemNo = tbl_PIC182_1.ItemNo
GROUP BY tbl_PIC182_1.ItemNo, tbl_PIC180_1.Company, tbl_PIC180_1.[Catalog]
)MaxDTRecord
on(MaxDTRecord.Date = dbo.tbl_PIC182.Date
and MaxDTRecord.ItemNo = dbo.tbl_PIC182.ItemNo
and MaxDTRecord.Company = dbo.tbl_PIC180.Company
and MaxDTRecord.[Catalog] = dbo.tbl_PIC180.[Catalog])

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 03:01:36

[code]SELECT *
FROM
(
SELECT ROW_NUMBER() OVER(PARTITION BY dbo.tbl_PIC180.ItemNo ORDER BY dbo.tbl_PIC182.Date DESC) AS Seq,dbo.tbl_PIC182.ItemDescription, dbo.tbl_PIC180.Company, dbo.tbl_PIC180.Catalog, dbo.tbl_PIC180.ItemNo, dbo.tbl_PIC180.Type, dbo.tbl_PIC180.[Group], dbo.tbl_PIC180.Location, dbo.tbl_PIC180.Page, dbo.tbl_PIC180.Commission, dbo.tbl_PIC180.UpsellPrefix, dbo.tbl_PIC180.UnitPrice2, dbo.tbl_PIC180.DateAdded, dbo.tbl_PIC180.DateModified, dbo.tbl_PIC180.ModifiedBy, dbo.tbl_PIC182.UpsellPrice, dbo.tbl_PIC182.Price AS UnitPrice, dbo.tbl_PIC182.Date AS Expr1

FROM
dbo.tbl_PIC180 INNER JOIN
dbo.tbl_PIC182
ON
dbo.tbl_PIC180.ItemNo = dbo.tbl_PIC182.ItemNo
)t
WHERE Seq=1[/code]

Drathnar
Starting Member

8 Posts

Posted - 2008-12-05 : 09:21:03

chrianth --

That is not really what i was looking for some items have a different date than the overall max date FEX..

Item 1 may have the most recent date of 12/5 where item 2 may have the most recent date of 11/3 I need to pull only the record with the most recent date for each item

same with your reply Vis it only pulls the records dated today and those are not the max records for all items some items are dated yesterday for example as their most recent update

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources