December 19, 2008 at 9:26 am
I could waffle on about my problem and not make any sense, so I will post the DDL and see what I can do to help you help me.
CREATE TABLE [dbo].[tblPricebookUpdates](
[pbu_manid] [int] NOT NULL,
[pbu_div] [int] NOT NULL,
[pbu_date] [smalldatetime] NOT NULL CONSTRAINT [DF_tblPricebookUpdates_pbu_date] DEFAULT (getdate()),
[pbu_url] [varchar](500) NULL,
[pbu_warning] [int] NULL,
CONSTRAINT [PK_tblPricebookUpdates] PRIMARY KEY CLUSTERED
(
[pbu_manid] ASC,
[pbu_div] ASC,
[pbu_date] ASC
) ON [PRIMARY]
) ON [PRIMARY]
Here is some data:
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 11,1,Nov 18 2008 5:06PM,null,null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 15,1,Dec 19 2008 12:14PM,null,1
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 21,1,Nov 18 2008 5:06PM,'/prices/pdf_prices/dts.pdf',null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 25,1,Nov 18 2008 5:06PM,'/prices/pdf_prices/wysiwyg.pdf',null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 25,1,Dec 1 2008 12:00AM,null,null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 26,1,Nov 18 2008 5:06PM,'/prices/pdf_prices/litec.pdf',null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 28,1,Nov 18 2008 5:06PM,null,null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 28,1,Dec 16 2008 1:08PM,null,null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 35,1,Jan 1 2008 12:00AM,null,null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 37,1,Nov 18 2008 5:06PM,null,null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 37,1,Dec 16 2008 4:38PM,null,null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 39,1,Nov 18 2008 5:06PM,'/prices/pdf_prices/spectralite.pdf',null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 45,1,Dec 19 2008 11:52AM,null,1
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 51,1,Nov 18 2008 5:06PM,null,null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 71,1,Dec 17 2008 10:00AM,null,null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 82,1,Nov 18 2008 5:06PM,'/prices/pdf_prices/diversitronics.pdf',null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 82,1,Dec 17 2008 8:53AM,null,null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 87,1,Dec 1 2008 5:02PM,'/prices/pdf_prices/doughty.pdf',null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 98,1,Dec 1 2008 5:02PM,'/prices/pdf_prices/thomas.pdf',null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 99,1,Nov 18 2008 5:06PM,'/prices/pdf_prices/clay_paky.pdf',null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 102,1,Dec 19 2008 12:04PM,null,1
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 104,1,Nov 18 2008 5:06PM,null,null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 104,1,Dec 16 2008 2:24PM,null,null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 142,1,Dec 19 2008 12:17PM,null,1
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 161,1,Oct 1 2008 12:00AM,null,null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 163,1,Dec 16 2008 5:12PM,null,null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 164,1,Nov 18 2008 5:06PM,null,null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 164,1,Dec 16 2008 4:42PM,null,null
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 165,1,Dec 19 2008 12:18PM,null,1
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 218,1,Dec 19 2008 12:06PM,null,1
insert tblPricebookUpdates ( pbu_manid,pbu_div,pbu_date,pbu_url,pbu_warning ) select 219,1,Dec 19 2008 12:12PM,null,1
There is another table involved which I wont show, which is joined to pbu_manid, I can do this once I have the basics sorted.
There is more data where pbu_div is another number, but this set will be filtered by this in the stored procedure, so isn't needed at this point.
I want to make a distinct list of pbu_manid along with the pbu_url (if it has one) but it has to the most current record, even if it doesn't have a pbu_url value.
This is what I tried
SELECT TOP 100 PERCENT pbu_manid, pbu_div, MAX(pbu_date) AS Newest, pbu_url
FROM tblPricebookUpdates
WHERE (pbu_div = 1)
GROUP BY pbu_div, pbu_url, pbu_manid
ORDER BY pbu_manid
But you can see that pbu_manid 25 and 82 is repeated twice. They should not be, only the most recent ones should be shown.
Any ideas? I think I need to forget the group by and aggregate, and use a sub query with top 1 ordered by date. But I can't quite see how. Any help would be appreciated.
Steve
December 19, 2008 at 10:53 am
I think I have it:
SELECT U.pbu_manid, dbo.tblManufacturer.web_manufacturer, U.pbu_div, U.pbu_url
FROM dbo.tblPricebookUpdates U INNER JOIN
dbo.tblManufacturer ON U.pbu_manid = dbo.tblManufacturer.web_manid JOIN
( SELECT X.pbu_manid, MAX(X.pbu_date) AS Latest FROM dbo.tblPricebookUpdates X GROUP BY X.pbu_manID
) D ON U.pbu_manid = D.pbu_manid AND U.pbu_date = D.Latest
WHERE (U.pbu_div = 1)
ORDER BY web_manufacturer
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply