Get minimum data without using MIN() function
-
All,
I have one scenario.
create table t1
(
AssetID int,
Date datetime,
InceptionDate datetime
)
insert into t1
select 1,'01/jan/2001','01/01/2005'
union
select 1,'01/feb/2002','01/01/2005'
union
select 1,'01/mar/2003','01/01/2005'
union
select 1,'01/apr/2004','01/01/2005'
union
select 2,'01/mar/2000','01/01/2003'
union
select 2,'01/apr/2000','01/01/2003'
union
select 3,'01/mar/2000','01/01/1999'
union
select 3,'01/apr/2000','01/01/1999'
i want to fetch only AssetID and min(Date) , If the date is less than inception data. But the condition is i don't want to use MIN() function.
Expected output:
1 01/01/2001
2 01/mar/2000
karthik
-
Actually the table has more than 100000 records. So if i execute query like
select Assetid,min(date)
from t1
group by assetid
having min(date) <> inceptiondate
it is taking 1 minutes.
karthik
-
Dear,
Kindly provide the test script for batter analyze.
Regards
Vijay
-
karthikeyan-444867 (3/25/2010)
Actually the table has more than 100000 records. So if i execute query likeselect Assetid,min(date)
from t1
group by assetid
having min(date) <> inceptiondate
it is taking 1 minutes.
.Net SqlClient Data Provider: Msg 8121, Level 16, State 1, Line 25Column 't1.InceptionDate' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
[/color]
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
CREATE TABLE #T1
(
AssetID INTEGER NOT NULL,
Date DATETIME NOT NULL,
InceptionDate DATETIME NOT NULL,
);
INSERT #T1
SELECT 1,'2001-01-01', '2005-01-01'
UNION ALL
SELECT 1,'2002-02-01', '2005-01-01'
UNION ALL
SELECT 1,'2003-03-01', '2005-01-01'
UNION ALL
SELECT 1,'2004-04-01', '2005-01-01'
UNION ALL
SELECT 2,'2000-03-01', '2003-01-01'
UNION ALL
SELECT 2,'2000-04-01', '2003-01-01'
UNION ALL
SELECT 3,'2000-03-01', '1999-01-01'
UNION ALL
SELECT 3,'2000-04-01', '1999-01-01';
SELECT AssetID, MIN(Date)
FROM #T1
GROUP BY
AssetID
HAVING MIN(Date) <> MIN(InceptionDate);
AssetID (No column name)
1 2001-01-01 00:00:00.000
2 2000-03-01 00:00:00.000
3 2000-03-01 00:00:00.000
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
1. Why do you want to avoid the min() function? That's like saying "I want to saw a board in half, but I don't want to use a saw." If this is a homework assignment, chances are that your professor recently went over the what he wants you to use in class.
2. Do you think min() is the reason you query takes a full minute to run? Is your question really just how to speed it up? Or is your query producing the wrong results?
3. having min(date) <> inceptiondate How does this test to see if the date is LESS than the inception date? The test you have specified is less than or greater than.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
DROP TABLE #T1
GO
CREATE TABLE #T1
(
AssetID INTEGER NOT NULL,
Date DATETIME NOT NULL,
InceptionDate DATETIME NOT NULL,
);
INSERT #T1
SELECT 1,'2001-01-01', '2005-01-01'
UNION ALL
SELECT 1,'2002-02-01', '2005-01-01'
UNION ALL
SELECT 1,'2003-03-01', '2005-01-01'
UNION ALL
SELECT 1,'2004-04-01', '2005-01-01'
UNION ALL
SELECT 2,'2000-03-01', '2003-01-01'
UNION ALL
SELECT 2,'2000-04-01', '2003-01-01'
UNION ALL
SELECT 3,'2000-03-01', '1999-01-01'
UNION ALL
SELECT 3,'2000-04-01', '1999-01-01';
CREATE INDEX nc1 ON #T1 (AssetID, InceptionDate);
CREATE UNIQUE INDEX uq1 ON #T1 (AssetID, Date);
SELECT A.AssetID, CA.Date
FROM (
SELECT DISTINCT
AssetID,
InceptionDate
FROM #T1
) A
CROSS
APPLY (
SELECT TOP (1)
B.Date
FROM #T1 B
WHERE B.AssetID = A.AssetID
AND B.Date < A.InceptionDate
ORDER BY
B.Date ASC
) CA;
AssetID Date
1 2001-01-01 00:00:00.000
2 2000-03-01 00:00:00.000
The stream aggregate there is for the DISTINCT, not a MIN.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
select assetid,date
from
(
select assetid,date, row_number () over (partition by assetid order by date ) rowid from t1
WHERE date < inceptiondate ) t
WHERE t.rowid = 1
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
karthikeyan-444867 (3/25/2010)
Actually the table has more than 100000 records. So if i execute query likeselect Assetid,min(date)
from t1
group by assetid
having min(date) <> inceptiondate
it is taking 1 minutes.
if you have index on date column then min(date) kills seek operation on it which might result in slownees of query.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-) -
Bhuvnesh (3/25/2010)
if you have index on date column then min(date) kills seek operation on it which might result in slownees of query.What do you mean here? Having an index prevents a seek???
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
Just for fun.
WITH [Recursive]
AS (
SELECT TOP (1)
T.AssetID,
T.Date
FROM #T1 T
WHERE T.Date < T.InceptionDate
ORDER BY
T.AssetID,
T.InceptionDate
UNION ALL
SELECT T.AssetID,
T.Date
FROM Recursive R
JOIN #T1 T
ON T.AssetID > R.AssetID
WHERE T.Date < T.InceptionDate
AND NOT EXISTS
(
SELECT *
FROM #T1 T1
WHERE T1.AssetID = T.AssetID
AND T1.Date < T1.InceptionDate
AND T1.Date < T.Date
)
AND NOT EXISTS
(
SELECT *
FROM #T1 T2
WHERE T2.AssetID < T.AssetID
AND T2.AssetID > R.AssetID
AND T2.Date < T2.InceptionDate
)
)
SELECT *
FROM Recursive;
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
I still don't understand why the obvious query is unacceptable.
select assetID, min([date])
from t1
where [date] < inceptionDate
group by assetID
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
The Dixie Flatline (3/25/2010)
I still don't understand why the obvious query is unacceptable.select assetID, min([date])
from t1
where [date] < inceptionDate
group by assetID
You have dealt with the 'Senior Software Engineer' before, right? 😉
Possibly a lack of good indexes, just as likely a data distribution which makes the (partial) index scan(s) inefficient. Depending on the circumstances, the TOP + APPLY may be significantly faster. There's a good article on why that might be around somewhere - :laugh:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi -
With a proper index in place, a TOP(1) subquery can beat the performance of a min(), because the query plan only has to do a one-row seek. I expect that one day they will refine the optimizer to the point where knows to do that with MIN() too.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills -
The Dixie Flatline (3/25/2010)
I expect that one day they will refine the optimizer to the point where knows to do that with MIN() too.That's called a loose index scan. There is a good reason why SQL Server does not include it, but I can't recall the details off the top of my head. I think it might be an architectural thing.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply