February 28, 2013 at 12:24 am
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 top 100 rows
Chris has compared all the methods for you in his post ( Post #1424431 ) along with sample data and results
All the methods including yours are logically the same and are giving the same results
Unless you are expecting some different result from the query, why are you stressing so much on using the TOP operator?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
February 28, 2013 at 1:05 am
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 top 100 rows
The minimum from the top 100 rows (as you've defined it) is exactly the same as the minimum from all rows. The selected row could change if you changed a sort order somewhere - but as it is, they are the same.
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
February 28, 2013 at 11:50 pm
thanks for replies
We want sorting sometimes,I think we will stick to
select min[v.date]
from
(select top 100 [date]
from
WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'
order by [date] desc) V
March 1, 2013 at 3:56 am
winmansoft (2/28/2013)
thanks for repliesWe want sorting sometimes,I think we will stick to
select min[v.date]
from
(select top 100 [date]
from
WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'
order by [date] desc) V
While reading all the post , I thought there would be a climax :w00t:
Just to explain a little bit :
"Top" operator let you select Top records from the provided "Order By" ..
so when you ..
select top 100 [date]
from
WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'
order by [date] desc
It displays a data set with a single column "Date" in descending order when date is in the range of
'01-Jan-2012' and '20-Jan-2013'
so if you want the minimum record in this range , logically , the last one is minimum ...
instead , if you write the query as
select top 100 [date]
from
WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'
order by [date] asc
then the first one is minimum ..
so just do a "select Top 1" ... and you get the minimim record in given date range..
and for the climax
were you looking to get the First minimum record in each 100 records ??
like
100 recs First minimum
100-200 recs First minimum
~ demonfox
___________________________________________________________________
Wondering what I would do next , when I am done with this one :ermm:
March 1, 2013 at 6:53 am
demonfox (3/1/2013)
winmansoft (2/28/2013)
thanks for repliesWe want sorting sometimes,I think we will stick to
select min[v.date]
from
(select top 100 [date]
from
WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'
order by [date] desc) V
While reading all the post , I thought there would be a climax :w00t:
Just to explain a little bit :
"Top" operator let you select Top records from the provided "Order By" ..
so when you ..
select top 100 [date]
from
WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'
order by [date] desc
It displays a data set with a single column "Date" in descending order when date is in the range of
'01-Jan-2012' and '20-Jan-2013'
so if you want the minimum record in this range , logically , the last one is minimum ...
instead , if you write the query as
select top 100 [date]
from
WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013'
order by [date] asc
then the first one is minimum ..
so just do a "select Top 1" ... and you get the minimim record in given date range..
and for the climax
were you looking to get the First minimum record in each 100 records ??
like
100 recs First minimum
100-200 recs First minimum
And that is part of the problem, the OP would tell us what the purpose was or what they were trying to accomplish.
March 3, 2013 at 9:50 pm
I have to get min date from top 100 rows which satisfies date condition..If i do
select top 1 [date] from
WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013' order by [date] asc
i think it will sort all dates(not only top 100 rows) meeting condition first and then selects min one.
March 4, 2013 at 1:06 am
winmansoft (3/3/2013)
I have to get min date from top 100 rows which satisfies date condition..
That is exactly what all the solutions suggested by Chris in Post #1424431 return.
If you feel otherwise, can you give us some DDL and sample data along with your expected results so that we can understand better.
winmansoft (3/3/2013)
select top 1 [date] fromWHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013' order by [date] asc
i think it will sort all dates(not only top 100 rows) meeting condition first and then selects min one.
No. It will not. Try running the query and check the execution plan.
You will find that the "WHERE Clause" filtering is done first and then the "ORDER BY" operation and finally the "SELECT".
One more question: Is it mandatory to use the TOP and ORDER BY Clause? If Yes, Why?
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 4, 2013 at 4:22 am
No,it is not mandatory to use the TOP and ORDER BY but they are required.. i can ditch order by by modifying query as "select min[a.date] from (select top 100 [date] from table where [date] BETWEEN '01-Jan-2012' and '20-Jan-2013')a". But i dont think i can ditch TOP. here is a example
If i have table with 1000 rows and say 10th row date is '03-01-12' and all other 100 dates are above '03-01-12' and 101th row date is '02-01-12'. your query "select top 1 [date] from
WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013' order by [date] asc" will be resulting in '02-01-12' not '03-01-12' which is minimum of 100 rows. . so i need TOP. I hope you understand.. Pls feel free to suggest any other queries for above condition
thank you
March 4, 2013 at 4:44 am
winmansoft (3/4/2013)
No,it is not mandatory to use the TOP and ORDER BY but they are required.. i can ditch order by by modifying query as "select min[a.date] from (select top 100 [date] from table where [date] BETWEEN '01-Jan-2012' and '20-Jan-2013')a". But i dont think i can ditch TOP. here is a exampleIf i have table with 1000 rows and say 10th row date is '03-01-12' and all other 100 dates are above '03-01-12' and 101th row date is '02-01-12'. your query "select top 1 [date] from
WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013' order by [date] asc" will be resulting in '02-01-12' not '03-01-12' which is minimum of 100 rows. . so i need TOP. I hope you understand.. Pls feel free to suggest any other queries for above condition
thank you
Is there any other column based on which you are deciding the 10th, 101th rows?
If you are using the date column, then the queries suggested till now are fine.
But, if there is some other column( eg: an id, code, etc. ) based on which you are deciding this, the query will have to be changed.
Can you give us some DDL, sample data and the desired results which will replicate your issue so that we can give you tested solutions back.
You can take the post by Chris above as an example or read the link in my signature on how to provide this.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
March 4, 2013 at 4:57 am
winmansoft (3/4/2013)
...If i have table with 1000 rows and say 10th row date is '03-01-12' and all other 100 dates are above '03-01-12' and 101th row date is '02-01-12'. your query "select top 1 [date] from
WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013' order by [date] asc" will be resulting in '02-01-12' not '03-01-12' which is minimum of 100 rows. . so i need TOP. I hope you understand.. Pls feel free to suggest any other queries for above condition
thank you
I think you are mistaken.
"10th row date " - 10th by what ordering scheme?
"101th row date is '02-01-12'" again, by what ordering scheme? There is no such thing as the "nth row" in a table. "nth row" makes no sense at all except in ordered output.
Without ORDER BY, the "TOP 100 rows" will be whatever SQL Server finds easiest (least expensive) to return and could differ between executions. It's most likely to be the top 100 rows ordered by the clustering key(s). If the table isn't clustered on the date column you are interested in, then for all intents and purposes the TOP 1 of this set - by date - is random.
If you still disagree, how about setting up a table containing 101 (or whatever) rows of data and we can all play with it. One of my posts above contains a sample table script.
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
March 4, 2013 at 4:59 am
winmansoft (3/3/2013)
I have to get min date from top 100 rows which satisfies date condition..If i doselect top 1 [date] from
WHERE [date] BETWEEN '01-Jan-2012' and '20-Jan-2013' order by [date] asc
i think it will sort all dates(not only top 100 rows) meeting condition first and then selects min one.
Queries are processed as follows: -
1. FROM
(a). Cartesian Product
(b). ON Filter
(c). Add Outer Rows
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
(a). Evaluate Expressions
(b). DISTINCT
(c). TOP
6. ORDER BY
Note that your "ORDER BY" is the final step, whilst the "WHERE" is the second step.
March 6, 2013 at 9:50 pm
Sorry for my misunderstanding..solutions suggested will work for those condition.. I have to get minimum date from top 100 max dates. Any query work for this?
March 6, 2013 at 10:04 pm
Does this get you started?
with TopDates as (
select top (100)
MyDates
from
dbo.MyTable
order by
MyDates desc
)
select min(MyDates) from TopDates;
March 7, 2013 at 11:14 pm
Lynn Pettis (3/6/2013)
Does this get you started?
with TopDates as (
select top (100)
MyDates
from
dbo.MyTable
order by
MyDates desc
)
select min(MyDates) from TopDates;
That will work with sql good. We have MSAccess database also.I don't think with TopDates will work with Access database or does it??
March 7, 2013 at 11:17 pm
winmansoft (3/7/2013)
Lynn Pettis (3/6/2013)
Does this get you started?
with TopDates as (
select top (100)
MyDates
from
dbo.MyTable
order by
MyDates desc
)
select min(MyDates) from TopDates;
That will work with sql good. We have MSAccess database also.I don't think with TopDates will work with Access database or does it??
I don't work with Access so I have no idea, plus this is posted in a SQL Server 2008 forum so expect SQL Server 2008 answers in response.
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply