[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Min and second Min

schaapiee

3/21/2007 2:35:00 PM

I want to grab the Min of a set of numbers, which works out fine. How
can I grab the second lowest number?


I tried to run another Min and exclude items from first Min,
logically this would exclude the lowest and the second lowest would
now be the first lowest, but it doesnt work.

Does anyone have any ideas on how to write this?
5 Answers

xyb

3/21/2007 2:40:00 PM

0

On 3?21?, ??10?35?, "schaapiee" <drobertmil....@yahoo.com> wrote:
> I want to grab the Min of a set of numbers, which works out fine. How
> can I grab the second lowest number?
>
> I tried to run another Min and exclude items from first Min,
> logically this would exclude the lowest and the second lowest would
> now be the first lowest, but it doesnt work.
>
> Does anyone have any ideas on how to write this?

create table #t
(c1 int)

insert into #t
select 1
union select 2
union select 3

select top 2 c1 from #t order by c1 asc

--more complicated :) meaning nothing
select min(c1) from #t
union select min(c1) from #t where c1 not in (select min(c1) from #t)

drop table #t

schaapiee

3/21/2007 3:38:00 PM

0

On Mar 21, 9:39 am, "xyb" <xiangyua...@gmail.com> wrote:
> On 3?21?, ??10?35?, "schaapiee" <drobertmil...@yahoo.com> wrote:
>
> > I want to grab the Min of a set of numbers, which works out fine. How
> > can I grab the second lowest number?
>
> > I tried to run another Min and exclude items from first Min,
> > logically this would exclude the lowest and the second lowest would
> > now be the first lowest, but it doesnt work.
>
> > Does anyone have any ideas on how to write this?
>
> create table #t
> (c1 int)
>
> insert into #t
> select 1
> union select 2
> union select 3
>
> select top 2 c1 from #t order by c1 asc
>
> --more complicated :) meaning nothing
> select min(c1) from #t
> union select min(c1) from #t where c1 not in (select min(c1) from #t)
>
> drop table #t

Thanks for the code.
The above works for a single variable, but I guess I wasnt specific
enough. I have two other columns which need to be included in the
select statements. So from the below example 1234 and 1888 are what we
are looking at.

Original Results: <Selecting Col1, Col2, c1 FROM whatever ORDER BY
Col1, Col2>
Col1 Col2 c1
A 1234 1
A 1234 2
A 1234 3
A 2222 1
A 2322 1
A 1122 600
B 1000 1
B 1888 1
B 1888 3
C 9999 1


MIN Run 1: MIN Run 2:
<Selecting Col1, Col2, Mc1=MIN(c1) <Dont know how to do...?
>
FROM whatever
GROUP BY Col1, Col2>

Col1 Col2 Mc1 Col1
Col2 Mc1
A 1234 1 A
1234 2
A 2222 1 B
1888 3
A 2322 1
A 1122 600
B 1000 1
B 1888 1
C 9999 1

Thanks again for any help!!!

Hugo Kornelis

3/21/2007 7:49:00 PM

0

On 21 Mar 2007 08:38:02 -0700, schaapiee wrote:

>The above works for a single variable, but I guess I wasnt specific
>enough.

Hi schaapiee (sounds Dutch - are you?),

That's why yoou should always provide all the details in your post :-)
Even better is to include CREATE TABLE and INSERT statements with sample
data as well, plus the expected results; that way we can cut and paste
the data into our databases and post a tested reply.

> I have two other columns which need to be included in the
>select statements.

So if I understand you correctly, you want the lowest and the
second-lowest value for c1 for any combination of Col1 and Col2, right?

Relatively easy if you can use SQL Server 2005:

WITH Ranked
AS
(SELECT Col1, Col2, c1,
ROW_NUMBER() OVER (PARTITION BY Col1, Col2
ORDER BY c1 ASC) AS rn
FROM YourTable)
SELECT Col1, Col2, rn, c1
FROM Ranked
WHERE rn IN (1, 2);
(untested)

It's a lot more work for SQL Server 2000, so I'll only do that if you
provide me with CREATE TABLE and INSERT statements and sample data, as
indicated above and on www.aspfaq.com/5006.

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hug...

Uri Dimant

3/22/2007 8:03:00 AM

0

In SQL Server 2000 you can do something like that

create table test (
c1 char(1),
c2 int
)

insert into test values('A',10)
insert into test values('A',8)
insert into test values('A',7)
insert into test values('B',12)
insert into test values('B',8)
insert into test values('B',6)
insert into test values('C',1)


SELECT * FROM test E
WHERE (
SELECT COUNT(*) FROM test
WHERE c1 = E.c1
AND c2 < E.c2
) <= 1
ORDER BY c1, c2 DESC



"schaapiee" <drobertmiller@yahoo.com> wrote in message
news:1174487722.741212.76630@n76g2000hsh.googlegroups.com...
>I want to grab the Min of a set of numbers, which works out fine. How
> can I grab the second lowest number?
>
>
> I tried to run another Min and exclude items from first Min,
> logically this would exclude the lowest and the second lowest would
> now be the first lowest, but it doesnt work.
>
> Does anyone have any ideas on how to write this?
>


schaapiee

3/22/2007 2:58:00 PM

0

On Mar 22, 3:02 am, "Uri Dimant" <u...@iscar.co.il> wrote:
> In SQL Server 2000 you can do something like that
>
> create table test (
> c1 char(1),
> c2 int
> )
>
> insert into test values('A',10)
> insert into test values('A',8)
> insert into test values('A',7)
> insert into test values('B',12)
> insert into test values('B',8)
> insert into test values('B',6)
> insert into test values('C',1)
>
> SELECT * FROM test E
> WHERE (
> SELECT COUNT(*) FROM test
> WHERE c1 = E.c1
> AND c2 < E.c2
> ) <= 1
> ORDER BY c1, c2 DESC
>
> "schaapiee" <drobertmil...@yahoo.com> wrote in message
>
> news:1174487722.741212.76630@n76g2000hsh.googlegroups.com...
>
>
>
> >I want to grab the Min of a set of numbers, which works out fine. How
> > can I grab the second lowest number?
>
> > I tried to run another Min and exclude items from first Min,
> > logically this would exclude the lowest and the second lowest would
> > now be the first lowest, but it doesnt work.
>
> > Does anyone have any ideas on how to write this?- Hide quoted text -
>
> - Show quoted text -

If I change the count to <= 2 it returns the top 2 values, but only if
there are two items to begin with.
I have a c2 that returns 5 lines, and so these are excluded from the
results. Any ideas?