Getting minimum of top n rows without using subquery

Question

Post reply

Getting minimum of top n rows without using subquery

IT researcher

SSCertifiable

Points: 7602
More actions
February 27, 2013 at 12:18 am

#271007

Hi,
We use SELECT min([date]) FROM table WHERE [date] IN (select top(100) [date] from table order by [date]) query for selecting minimum date from top hundred rows. Is there any other way to do same task in single query without using sub query in IN?
Thank you

Viewing 15 posts - 1 through 15 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

What is the need to use a sub-query?

I don't see any difference between the below queries or am I missing something.

SELECT min([date]) FROM table WHERE [date] IN (select top(100) [date] from table order by [date])

SELECT min([date]) FROM table

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

IT researcher SSCertifiable Points: 7602 More actions · Answer 2

sorry for that,

Actually my query contains where clause in sub query

SELECT min([date]) FROM entries WHERE [date] IN (select top(100) [date] from entries WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013' order by [date]);

I believe this makes difference

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

Yes. It does and you can simplify it further as below

select min([date]) from entries WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

IT researcher SSCertifiable Points: 7602 More actions · Answer 4

I don't think that is correct,because our query returns min(date) from top 100 rows which satisfies where condition But i don't find that in your query

Gazareth One Orange Chip Points: 27737 More actions · Answer 5

Your ascending ordering in the subquery means they're logically the same. If you were ordering by date descending then that would be a different matter.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 6

Gazareth (2/27/2013)
Your ascending ordering in the subquery means they're logically the same. If you were ordering by date descending then that would be a different matter.

Exactly.

winmansoft (2/27/2013)
I don't think that is correct,because our query returns min(date) from top 100 rows which satisfies where condition But i don't find that in your query

Is the date ordered in ASC order or DESC order?

Since nothing is mentioned in particular in your query, SQL Server will assume it to be ASC by default.

If the ordering is DESC, then removing the subquery is not possible

You will have to use subquery or a CTE( which again will be a sort of sub query ).

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

IT researcher SSCertifiable Points: 7602 More actions · Answer 7

What if we don't want to sort the date? Is there any easy method?

Gazareth One Orange Chip Points: 27737 More actions · Answer 8

Hi,

At this point, it might be easier to tell us exactly what date you're trying to get from the table 🙂

Kingston's solution seems to correct to me but if that's not what you need then we need more information.

Thanks

Gaz

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

winmansoft (2/27/2013)
What if we don't want to sort the date? Is there any easy method?

Use Kingston's solution. It doesn't use the sort operator. It uses a stream aggregate.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

winmansoft (2/27/2013)
I don't think that is correct,because our query returns min(date) from top 100 rows which satisfies where condition But i don't find that in your query

Looks good to me...

-- create a table with a column [date]

-- containing 10,000 dates between 1986-01-20 and 2013-06-06

/*

DROP TABLE #Entries;

WITH E1(N) AS (

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL

SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1

), --10E+1 or 10 rows

E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows

E4(N) AS (SELECT 1 FROM E2 a, E2 b) --10E+4 or 10,000 rows max

SELECT [date] = DATEADD(dd,0-ROW_NUMBER() OVER(ORDER BY N)+100,CAST(GETDATE() AS DATE))

INTO #Entries

FROM E4

*/

------------------------------------------------------------------------------------------

-- 1. OP's original query (with added filter) 39% of total cost

SELECT min([date])

FROM #entries

WHERE [date] IN (

select top(100) [date]

from #entries

WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'

order by [date]);

-- returns 2012-01-01

-- 2. how about this: 32% of total cost

SELECT min([date])

FROM #entries

WHERE [date] IN (

select top(1) [date]

from #entries

WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'

order by [date]);

-- returns 2012-01-01

-- 3. Kingston's solution: 13% of total cost

select min([date])

from #entries

WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'

-- returns 2012-01-01

-- another solution (see 2.): 16% of total cost

select top(1) [date]

from #entries

WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'

order by [date]

-- returns 2012-01-01

-- ALL FOUR QUERIES run in less than a second.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

IT researcher SSCertifiable Points: 7602 More actions · Answer 11

You mean "select min([date]) from entries WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'"? But we want to select minimum from top 100 rows

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

winmansoft (2/27/2013)
You mean "select min([date]) from entries WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'"? But we want to select minimum from to 100 rows

Then add the TOP and the ORDER BY back in.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

IT researcher SSCertifiable Points: 7602 More actions · Answer 13

Thanks for replies

How to add Top into "select min([date]) from entries WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'" without using sub query?

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Why not explain exactly what it is you are trying to accomplish? I have no idea so I have no idea what to tell you until you can provide us with more information.