September 15, 2005 at 4:26 am
Hi friends.
i want to find Max n Min in a particular column (for a series of values in columns)
If data is 100,103,105,107,108,109,110,111,112,115
then i want the minimum and the highest no in series. Min is 107, Max is : 112
and also the count of other values.
pl. help.
thnx
September 15, 2005 at 5:03 am
So you want the min, max and count, subject to a filter on the data that is being analysed? Like this?
select count(field), min(field), max(field) from table where field between 107 and 112
Regards
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 15, 2005 at 5:41 am
107 and 112 values are not sure.
And also the series of values will vary depending on the values in the columns. This series can be more than one in a column.
eg. i can find series from 107 to 112. And also another series from 115 to 119 in the column.
thnx.
September 15, 2005 at 5:54 am
Aah! So you are trying to detect series of consecutive numbers that do not contain gaps?
So you want output like this:
Min Max Count
107 112 6
?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
September 15, 2005 at 6:00 am
Yes. the same output u have shown is perfect.
September 15, 2005 at 8:36 am
select top 1 a.value,b.value,count(*)
from [Table] a
cross join [Table] b
inner join [Table] c on c.value between a.value and b.value
where b.value > a.value
group by a.value,b.value
having count(*) = b.value-a.value+1
order by count(*) desc
p.s. (added)
Put the data in a table, if not already
Far away is close at hand in the images of elsewhere.
Anon.
September 15, 2005 at 10:51 pm
Thnx David.
It is working.
The TOP 1 clause gives me the first series only. I need to know the exact number of series in the data if it has to work perfectly.
If TOP 2 is not used, then all the series is displayed.
Is it possible to restrict the values which have once appeared in a particular series so that i only get the no. of series which are there.
September 16, 2005 at 3:05 am
create table #temp (minval int, maxval int, series int)
insert into #temp
select a.value,b.value,count(*)
from [Table] a
cross join [Table] b
inner join [Table] c on c.value between a.value and b.value
where b.value > a.value
group by a.value,b.value
having count(*) = b.value-a.value+1
order by count(*) desc,a.value
select a.minval,a.maxval,a.series
from #temp a
left outer join #temp b
on b.series>a.series
and (a.minval between b.minval and b.maxval)
and (a.maxval between b.minval and b.maxval)
where b.series is null
drop table #temp
Far away is close at hand in the images of elsewhere.
Anon.
September 16, 2005 at 5:27 am
gr8 david.
thnx a lot .. it's working...
September 16, 2005 at 5:48 am
Hi david...
thnx for ur help again.
Have to done some querry optimization stuff.
It would be nice of u if you give me some tips for optimizing querry or even how to interpret execution plans.
Again thnx,
September 16, 2005 at 6:46 am
First look at what costs the most in the plan
If you look at the plan for the first select query there are 3 table scans, which means that each row in the table is read to satisfy that part of the query.
By adding a primary key/index to the table
one table scan will change to index scan
(the index is used instead of the data to pick required rows)
and two will change to index seek
(the index is used to find the row directly which is the most efficient)
The plan for the second query shows two table scans with the most cost
By adding an index
create clustered index ix_temp on #temp (series,minval,maxval)
one will change to index scan, the other to index seek
You need to experiment with this to see if performance improves.
Eventually, and hopefully, you will see at least one part of the query has the most cost (and the rest small/insignificant) this is probably the part that cannot be changed.
This site will have lots of info on tuning and interpreting plans, try searching for parts of the plan that costs most (ie Bookmark Lookups etc), this should give you explanations and possible answers.
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply