September 11, 2018 at 12:20 pm
Hi,
Is there another way of writing this query without first having to get the MAX date for an id and then get the record for that id?
SELECT t0.IdSeller, t0.ProductsCount, t0.AvgPrice FROM SellerProductsSnapshots t0
INNER JOIN (
SELECT IdSeller, MAX(Date) date FROM SellerProductsSnapshots
WHERE IdSeller IN (SELECT Id FROM #SellersTable)
GROUP BY IdSeller
) t1 ON t0.IdSeller = t1.IdSeller AND t1.date = t0.Date
I could do it with ROW_NUMBER and PARTITION BY id ORDER BY date DESC and get the first row but wouldn't that be the same?
Thanks,
Pedro
September 11, 2018 at 12:30 pm
This may get the same thing, without data I can't test:SELECT t0.IdSeller, t0.ProductsCount, t0.AvgPrice,
MAX(Date) AS Maxdate
FROM SellerProductsSnapshots t0
WHERE IdSeller IN (SELECT Id FROM #SellersTable)
GROUP BY IdSeller, t0.ProductsCount, t0.AvgPrice
I would avoid the ROW_Number option, only when no other way exists.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
September 11, 2018 at 12:52 pm
below86 - Tuesday, September 11, 2018 12:30 PMThis may get the same thing, without data I can't test:SELECT t0.IdSeller, t0.ProductsCount, t0.AvgPrice,
MAX(Date) AS Maxdate
FROM SellerProductsSnapshots t0
WHERE IdSeller IN (SELECT Id FROM #SellersTable)
GROUP BY IdSeller, t0.ProductsCount, t0.AvgPrice
I would avoid the ROW_Number option, only when no other way exists.
Duhhhh...
Thanks... Didn't see that one... 😀
September 11, 2018 at 1:20 pm
No problem, sometimes helps to have another set of eyes.
See if this would be any faster:SELECT t0.IdSeller, t0.ProductsCount, t0.AvgPrice,
MAX(Date) AS Maxdate
FROM SellerProductsSnapshots t0
INNER JOIN #SellersTable
ON IdSeller = Id
GROUP BY IdSeller, t0.ProductsCount, t0.AvgPrice
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
September 11, 2018 at 7:45 pm
since you didn’t bother to follow forum rules. I guess we’ll just have to guess at the keys and everything else that you fail to post. You have some obvious problems. First of all, there can never be a data element named just “date†because a date is a unit of temporal measure, and never a data element. When you get a good book on data modeling, you’ll learn about attribute properties. This means that the date property has to belong to a particular attribute (hire_date, termination_date, birth_date, etc.) and since you failed to tell us, all guess that is the date of the sale.
Another principle is DRY, which stands for “don’t repeat yourselfâ€; this means that the table name should appear only once in a query. Using table aliases that give absolutely no clue as to what set of entities or what relationship the table models is pretty useless. In particular, putting a number mimics the way tape drives were named back in the 1960s on IBM systems (MT0, MT1, MT2, etc) Is this why you confused records (mag taps) with rows?
Mixing data and metadata in a data element name is a horrible design flaw. In fact, putting the word “_table†or some variation of it is so bad it has a name; it’s called a Tibble and we laugh at it.
We also don’t like the use of temporary tables. This is how non-SQL programmers fake their familiar scratch tapes in their new language.
>> I could do it with ROW_NUMBER and PARTITION BY id ORDER BY date DESC and get the first row but wouldn't that be the same? <<
Why do all that extra work? You only need the most recent sale date. Why would you want to ask for an extra sort instead of just the max?
WITH Recent_Sales
AS
(SELECT SP.seller_id, SP.product_cnt, SP.sale_price_avg,
SP.sale_date, MAX(sale_date) OVER() AS sale_date_max
FROM SellerProducts AS SP)
SELECT seller_id, product_cnt, sale_price_avg, sale_date
FROM Recent_Sales
WHERE sale_date = sale_date_max
AND seller_id IN ( ??? );
I’m not sure how to get that list of seller_id’s, but I do know the name of the column would not become “id†in one place in your schema and then suddenly become a “seller_id†somewhere else.
Please post DDL and follow ANSI/ISO standards when asking for help.
September 14, 2018 at 7:51 am
...and so, we have a perfect example of someone trying to help - and another person, not.
September 14, 2018 at 2:12 pm
In addition to the ROW_NUMBER method offered up by below86, here are two additional methods... One is usually faster than the others depending on existing indexes so all are worth testing.
-- Option 1
SELECT
IdSeller = st.Id,
spsx.ProductsCount
spsx.AvgPrice
FROM
#SellersTable st --(assuming the id is already unique)
CROSS APPLY (
SELECT TOP (1)
sps.ProductsCount,
sps.AvgPrice
FROM
dbo.SellerProductsSnapshots sps
ORDER BY
sps.Date DESC
WHERE
st.id = sps.IdSeller
) spsx;
-- Option 2
SELECT
IdSeller = st.Id,
ProductsCount = CONVERT(INT, SUBSTRING(spsx.max_bin_val, 9, 4)),
AvgPrice = CONVERT(MONEY, SUBSTRING(spsx.max_bin_val, 13, 8))
FROM
#SellersTable st --(assuming the id is already unique)
CROSS APPLY (
SELECT
MAX(CONVERT(BINARY(8), sps.date) + CONVERT(BINARY(4), sps.ProductsCount) + CONVERT(BINARY(8), sps.AvgPrice))
FROM
dbo.SellerProductsSnapshots sps
GROUP BY
sps.IdSeller
WHERE
st.id = sps.IdSeller
) spsx (max_bin_val);
September 16, 2018 at 9:40 am
jcelko212 32090 - Tuesday, September 11, 2018 7:45 PMI’m not sure how to get that list of seller_id’s, but I do know the name of the column would not become “id†in one place in your schema and then suddenly become a “seller_id†somewhere else.
Now THAT I most definitely agree with! I'm currently plagued by a 3rd party database where they did that despite my warnings during the initial meeting on development of the database. The have the following column names and they are all for the same thing...
BatchID
Batch_ID
Batch
BatchNum
BatchNo
It's just stupid and certainly shows that the vendor didn't and doesn't actually have their act together. That bit of reflection also explains why their code sucks for performance. No real plan, no real design, and no real concern. At least they didn't try to abbreviate the word "Batch".
--Jeff Moden
Change is inevitable... Change for the better is not.
September 17, 2018 at 8:19 am
Jason A. Long - Friday, September 14, 2018 2:12 PMIn addition to the ROW_NUMBER method offered up by below86, ...
I said to avoid the Row_Number, I avoid it like the plaque. The original requester mentioned using it.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
September 17, 2018 at 8:33 am
below86 - Monday, September 17, 2018 8:19 AMJason A. Long - Friday, September 14, 2018 2:12 PMIn addition to the ROW_NUMBER method offered up by below86, ...I said to avoid the Row_Number, I avoid it like the plaque. The original requester mentioned using it.
You're right... Apparently I read through the existing replies too quickly. Sorry about that.
That said, if the correct "POC" index is in place the ROW_NUMBER() method can be an extremity performant solution... As good or better than any of the other solutions that have been offered up on this thread. Certainly not something to "avoid like the plague".
September 17, 2018 at 9:16 am
PiMané - Tuesday, September 11, 2018 12:20 PMHi,Is there another way of writing this query without first having to get the MAX date for an id and then get the record for that id?
SELECT t0.IdSeller, t0.ProductsCount, t0.AvgPrice FROM SellerProductsSnapshots t0
INNER JOIN (
SELECT IdSeller, MAX(Date) date FROM SellerProductsSnapshots
WHERE IdSeller IN (SELECT Id FROM #SellersTable)
GROUP BY IdSeller
) t1 ON t0.IdSeller = t1.IdSeller AND t1.date = t0.Date
I could do it with ROW_NUMBER and PARTITION BY id ORDER BY date DESC and get the first row but wouldn't that be the same?
Thanks,
Pedro
This will be the fastest if you have more than about 5 rows per IdSeller and you create the indexes below.CREATE INDEX IX_SellerProductsSnapshots_1 ON SellerProductsSnapshots(IdSeller,[Date]) INCLUDE (ProductsCount,AvgPrice);
CREATE INDEX IX_#SellersTable_1 ON #SellersTable(Id);
;WITH CTE AS
(
SELECT DISTINCT
sps.IdSeller
FROM SellerProductsSnapshots sps
WHERE EXISTS(SELECT *
FROM #SellersTable st
WHERE st.Id = sps.IdSeller)
)
SELECT sps.IdSeller,
sps2.ProductsCount,
sps2.AvgPrice
FROM CTE sps
CROSS APPLY(SELECT TOP(1)
sps2.ProductsCount,
sps2.AvgPrice,
sps2.[date]
FROM SellerProductsSnapshots sps2
WHERE sps2.IdSeller = sps.IdSeller
ORDER BY sps2.Date DESC) AS sps2;
September 17, 2018 at 9:34 am
Jonathan AC Roberts - Monday, September 17, 2018 9:16 AMThis will be the fastest if you have more than about 5 rows per IdSeller and you create the indexes below.
Bold claim... Is there, by chance, a test harness to back it up?
September 17, 2018 at 10:33 am
Jason A. Long - Monday, September 17, 2018 8:33 AMbelow86 - Monday, September 17, 2018 8:19 AMJason A. Long - Friday, September 14, 2018 2:12 PMIn addition to the ROW_NUMBER method offered up by below86, ...I said to avoid the Row_Number, I avoid it like the plaque. The original requester mentioned using it.
You're right... Apparently I read through the existing replies too quickly. Sorry about that.
That said, if the correct "POC" index is in place the ROW_NUMBER() method can be an extremity performant solution... As good or better than any of the other solutions that have been offered up on this thread. Certainly not something to "avoid like the plague".
I've seen it used as a crutch to get unique rows when there are better ways. I'll avoid it like the plague and plaque, I brush often. 😀
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
September 17, 2018 at 10:41 am
Jason A. Long - Monday, September 17, 2018 9:34 AMJonathan AC Roberts - Monday, September 17, 2018 9:16 AMThis will be the fastest if you have more than about 5 rows per IdSeller and you create the indexes below.Bold claim... Is there, by chance, a test harness to back it up?
Ok some test data:
IF OBJECT_ID('tempdb..#SellersTable') IS NOT NULL
DROP TABLE #SellersTable
DECLARE @RowsToInsert as int = 1000000
;WITH A(A) AS
(SELECT 'Anything' FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) A(A)),
B(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1 A))-1 FROM A A, A B, A C, A D, A E, A F, A G, A H)
SELECT TOP(@RowsToInsert) N Id
INTO #SellersTable
FROM B
IF OBJECT_ID('dbo.SellerProductsSnapshots') IS NOT NULL
DROP TABLE SellerProductsSnapshots
CREATE TABLE SellerProductsSnapshots
(
IdSeller int NOT NULL,
ProductsCount int NOT NULL,
AvgPrice money NOT NULL,
Date datetime NOT NULL
)
GO
INSERT INTO SellerProductsSnapshots
(
IdSeller,
ProductsCount,
AvgPrice,
Date
)
SELECT Id,
ABS(CHECKSUM(NewId())) % 50 + 5,
ABS(CHECKSUM(NewId())) % 100,
DATEADD(dd,ABS(CHECKSUM(NewId())) % 100,'20180101')
FROM #SellersTable
GO 6
CREATE INDEX IX_SellerProductsSnapshots_1 ON SellerProductsSnapshots(IdSeller,[Date]) INCLUDE (ProductsCount,AvgPrice);
CREATE INDEX IX_#SellersTable_1 ON #SellersTable(Id);
Select query;WITH CTE AS
(
SELECT DISTINCT
sps.IdSeller
FROM SellerProductsSnapshots sps
WHERE EXISTS(SELECT *
FROM #SellersTable st
WHERE st.Id = sps.IdSeller)
)
SELECT sps.IdSeller,
sps2.ProductsCount,
sps2.AvgPrice
FROM CTE sps
CROSS APPLY(SELECT TOP(1)
sps2.ProductsCount,
sps2.AvgPrice,
sps2.[date]
FROM SellerProductsSnapshots sps2
WHERE sps2.IdSeller = sps.IdSeller
ORDER BY sps2.Date DESC) AS sps2;
September 17, 2018 at 11:54 am
Start by adding a POC index...
--POC index...
CREATE NONCLUSTERED INDEX ix_SellerProductsSnapshots_POC
ON dbo.SellerProductsSnapshots (
IdSeller ASC,
Date DESC
)
INCLUDE (
ProductsCount,
AvgPrice
);
Note: setting SSMS to "Discard results after execution" to prevent display rendering from impacting results..
SET NOCOUNT ON;
GO
GO
-- DBCC FLUSHPROCINDB(16) WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
--EXEC master.dbo.sp_DBCC_DropCleanBuffers_FreeProcCache;
PRINT(CONCAT(CHAR(13), CHAR(10), N' wait a moment...', CHAR(13), CHAR(10)));
WAITFOR DELAY '00:00:01';
GO
-- SET STATISTICS XML ON;
GO
DECLARE @_clock_start DATETIME2(7) = SYSDATETIME(), @_test_name NVARCHAR(70) = N' ';
PRINT(CONCAT(N'+', REPLICATE(N'-', 148), N'+
¦', LEFT(CONCAT(N' Start Time: ', @_clock_start, N' Test Name: ', ISNULL(NULLIF(@_test_name, N' '), N'Test Query 1'),
REPLICATE(N' ', 100)), 148), N'¦', CHAR(13), CHAR(10), N'+', REPLICATE(N'-', 148), N'+')); DECLARE @_data_dump_int INT; SET STATISTICS IO ON;
-- ____________________________________________________________________________________________________________________________________________
-- ?????? place tsql here ????????????????????????????????????????????????????????????
;WITH CTE AS
(
SELECT DISTINCT
sps.IdSeller
FROM SellerProductsSnapshots sps
WHERE EXISTS(SELECT *
FROM #SellersTable st
WHERE st.Id = sps.IdSeller)
)
SELECT sps.IdSeller,
sps2.ProductsCount,
sps2.AvgPrice
FROM CTE sps
CROSS APPLY(SELECT TOP(1)
sps2.ProductsCount,
sps2.AvgPrice,
sps2.Date
FROM SellerProductsSnapshots sps2
WHERE sps2.IdSeller = sps.IdSeller
ORDER BY sps2.Date DESC) AS sps2;
-- ?????? place tsql here ????????????????????????????????????????????????????????????
-- ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
DECLARE @_clock_stop DATETIME2(7) = SYSDATETIME(); SET STATISTICS IO OFF; PRINT(CONCAT(N'+', REPLICATE(N'-', 148), N'+
¦', LEFT(STUFF(CONCAT(N' Finish Time: ', @_clock_stop, N' Duration: ', DATEDIFF(mcs, @_clock_start, @_clock_stop)
/ 1000000.0, N' secs. ', DATEDIFF(mcs, @_clock_start, @_clock_stop) / 1000.0, N' ms.', REPLICATE(N' ', 100)), 76, 3, N''), 148), N'¦
+', REPLICATE(N'-', 148), N'+'));
GO
-- SET STATISTICS XML OFF;
GO
-- DBCC FLUSHPROCINDB(16) WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
--EXEC master.dbo.sp_DBCC_DropCleanBuffers_FreeProcCache;
PRINT(CONCAT(CHAR(13), CHAR(10), N' wait a moment...', CHAR(13), CHAR(10)));
WAITFOR DELAY '00:00:01';
GO
-- SET STATISTICS XML ON;
GO
DECLARE @_clock_start DATETIME2(7) = SYSDATETIME(), @_test_name NVARCHAR(70) = N' ';
PRINT(CONCAT(N'+', REPLICATE(N'-', 148), N'+
¦', LEFT(CONCAT(N' Start Time: ', @_clock_start, N' Test Name: ', ISNULL(NULLIF(@_test_name, N' '), N'Test Query 2'),
REPLICATE(N' ', 100)), 148), N'¦', CHAR(13), CHAR(10), N'+', REPLICATE(N'-', 148), N'+')); DECLARE @_data_dump_int INT; SET STATISTICS IO ON;
-- ____________________________________________________________________________________________________________________________________________
-- ?????? place tsql here ????????????????????????????????????????????????????????????
WITH
cte_AddRN AS (
SELECT
sps.IdSeller,
sps.ProductsCount,
sps.AvgPrice,
sps.Date,
rn= ROW_NUMBER() OVER (PARTITION BY sps.IdSeller ORDER BY sps.Date DESC)
FROM
dbo.SellerProductsSnapshots sps
)
SELECT
IdSeller = st.Id, arn.ProductsCount, arn.AvgPrice
FROM
#SellersTable st
INNER MERGE JOIN cte_AddRN arn -- (since both tables are order on Id (IdSeller a MERGE join is appropriate...
ON st.Id = arn.IdSeller
WHERE
arn.rn = 1;
-- ?????? place tsql here ????????????????????????????????????????????????????????????
-- ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
DECLARE @_clock_stop DATETIME2(7) = SYSDATETIME(); SET STATISTICS IO OFF; PRINT(CONCAT(N'+', REPLICATE(N'-', 148), N'+
¦', LEFT(STUFF(CONCAT(N' Finish Time: ', @_clock_stop, N' Duration: ', DATEDIFF(mcs, @_clock_start, @_clock_stop)
/ 1000000.0, N' secs. ', DATEDIFF(mcs, @_clock_start, @_clock_stop) / 1000.0, N' ms.', REPLICATE(N' ', 100)), 76, 3, N''), 148), N'¦
+', REPLICATE(N'-', 148), N'+'));
GO
-- SET STATISTICS XML OFF;
GO
-- DBCC FLUSHPROCINDB(16) WITH NO_INFOMSGS;
DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;
--EXEC master.dbo.sp_DBCC_DropCleanBuffers_FreeProcCache;
PRINT(CONCAT(CHAR(13), CHAR(10), N' wait a moment...', CHAR(13), CHAR(10)));
WAITFOR DELAY '00:00:01';
GO
-- SET STATISTICS XML ON;
GO
DECLARE @_clock_start DATETIME2(7) = SYSDATETIME(), @_test_name NVARCHAR(70) = N' ';
PRINT(CONCAT(N'+', REPLICATE(N'-', 148), N'+
¦', LEFT(CONCAT(N' Start Time: ', @_clock_start, N' Test Name: ', ISNULL(NULLIF(@_test_name, N' '), N'Test Query 3'),
REPLICATE(N' ', 100)), 148), N'¦', CHAR(13), CHAR(10), N'+', REPLICATE(N'-', 148), N'+')); DECLARE @_data_dump_int INT; SET STATISTICS IO ON;
-- ____________________________________________________________________________________________________________________________________________
-- ?????? place tsql here ????????????????????????????????????????????????????????????
SELECT
IdSeller = st.Id,
ProductsCount = CONVERT(INT, SUBSTRING(spsx.max_bin_val, 9, 4)),
AvgPrice = CONVERT(MONEY, SUBSTRING(spsx.max_bin_val, 13, 8))
FROM
#SellersTable st
CROSS APPLY (
SELECT
MAX(CONVERT(BINARY(8), sps.Date) + CONVERT(BINARY(4), sps.ProductsCount) + CONVERT(BINARY(8), sps.AvgPrice))
FROM
dbo.SellerProductsSnapshots sps
WHERE
st.Id = sps.IdSeller
) spsx (max_bin_val);
-- ?????? place tsql here ????????????????????????????????????????????????????????????
-- ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????
DECLARE @_clock_stop DATETIME2(7) = SYSDATETIME(); SET STATISTICS IO OFF; PRINT(CONCAT(N'+', REPLICATE(N'-', 148), N'+
¦', LEFT(STUFF(CONCAT(N' Finish Time: ', @_clock_stop, N' Duration: ', DATEDIFF(mcs, @_clock_start, @_clock_stop)
/ 1000000.0, N' secs. ', DATEDIFF(mcs, @_clock_start, @_clock_stop) / 1000.0, N' ms.', REPLICATE(N' ', 100)), 76, 3, N''), 148), N'¦
+', REPLICATE(N'-', 148), N'+'));
GO
-- SET STATISTICS XML OFF;
GO
The results
wait a moment...
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Start Time: 2018-09-17 13:40:04.7958330 Test Name: Test Query 1 ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
Table 'SellerProductsSnapshots'. Scan count 1000001, logical reads 3196940, physical reads 1, read-ahead reads 4719, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#SellersTable_______________________________________________________________________________________________________0000000049C6'. Scan count 1, logical reads 2733, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Finish Time: 2018-09-17 13:40:08.8240652 Duration: 4.028232 secs. 4028.232000 ms. ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
wait a moment...
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Start Time: 2018-09-17 13:40:10.0431337 Test Name: Test Query 2 ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SellerProductsSnapshots'. Scan count 1, logical reads 4718, physical reads 1, read-ahead reads 4711, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table '#SellersTable_______________________________________________________________________________________________________0000000049C6'. Scan count 1, logical reads 2733, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Finish Time: 2018-09-17 13:40:12.2462594 Duration: 2.203126 secs. 2203.126000 ms. ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
wait a moment...
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Start Time: 2018-09-17 13:40:13.5003326 Test Name: Test Query 3 ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
Table '#SellersTable_______________________________________________________________________________________________________0000000049C6'. Scan count 1, logical reads 2733, physical reads 0, read-ahead reads 16, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'SellerProductsSnapshots'. Scan count 1, logical reads 4718, physical reads 1, read-ahead reads 4711, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
+----------------------------------------------------------------------------------------------------------------------------------------------------+
¦ Finish Time: 2018-09-17 13:40:15.2564298 Duration: 1.756097 secs. 1756.097000 ms. ¦
+----------------------------------------------------------------------------------------------------------------------------------------------------+
Viewing 15 posts - 1 through 15 (of 23 total)
You must be logged in to reply to this topic. Login to reply