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


 

Forums >

microsoft.public.sqlserver.programming

min()/max()/top 1, subqueries, or what?

rebump

3/20/2007 2:33:00 AM

I haven't had to do much funky SQL in a couple years now, mostly just
straightforward stored procedures for use in .NET code.

However, I have a query that is giving me fits or I am just having one
of those days.

I will not share my multitude of attempts so I do not get you thinking
along the (wrong) lines I apparently am thinking along.

The gist of it is:

I have a table that has what you could consider a composite key, say
ID1, ID2, and ID3. I need to get the MIN value for ID3 and then the
MIN value of ID2 where still in the MIN value of ID3 and then of
course ID1.

Thusly, if my data looks like this:

ID1, ID2, ID3, ...
==========
1005, 1, 37
1005, 2, 0
1005, 3, 0
1005, 4, 54
1005, 5, 38

1007, 1, 2

1009, 1, 1
1009, 2, 3

1010, 1, 1
1010, 2, 1
1010, 3, 2
1010, 4, 3

I need to be able to pull out these records in a single (and hopefully
effecient) query:

1005, 2, 0 (i.e. lowest ID3, then lowest ID2, for given ID1 and only
*one* record for given ID1 - not to include 1005, 3, 0)

1007, 1, 2

1009, 1, 1

1010, 1, 1

And that is it. I have been using subqueries and such with variations
of "top 1" with multi-column sort orders. I have tried using MIN() in
various ways too (within subqueries and such as well). I think I went
down a wrong path and cannot come back. I may just need to clear my
head for the evening and come back tomorrow morning but in case I do
and I still cannot figure it out, maybe you fine folks could beat me
to it.

SQL seems to be one of those use it or lose it things - at least for
subquery and/or nesting type queries.

Thanks in advance,
rebump
10 Answers

rebump

3/20/2007 2:37:00 AM

0


I should add to the above...SQL Server 2000 (i.e. no 2005 :( )

Thanks again,
rebump

xyb

3/20/2007 3:07:00 AM

0

On 3?20?, ??10?37?, "rebump" <reb...@gmail.com> wrote:
> I should add to the above...SQL Server 2000 (i.e. no 2005 :( )
>
> Thanks again,
> rebump
the main conception is to count mininum of mininum of id3,test code
here:
create table #t
(
ID1 int,
ID2 int,
ID3 int
)

insert into #t
select 1005, 1, 37
union select 1005, 2, 0
union select 1005, 3, 0
union select 1005, 4, 54
union select 1005, 5, 38
union select 1007, 1, 2
union select 1009, 1, 1
union select 1009, 2, 3
union select 1010, 1, 1
union select 1010, 2, 1
union select 1010, 3, 2
union select 1010, 4, 3


select * from #t

select id1,mid2=min(id2),mmid3 = min(mid3) from
(
select id1,id2,mid3=min(id3) from #t group by id1,id2 --order by id1
) as n1
group by id1

drop table #t

rebump

3/20/2007 3:15:00 AM

0

On Mar 19, 10:06 pm, "xyb" <xiangyua...@gmail.com> wrote:
> On 3?20?, ??10?37?, "rebump" <reb...@gmail.com> wrote:> I should add to the above...SQL Server 2000 (i.e. no 2005 :( )
>
> > Thanks again,
> > rebump
>
> the main conception is to count mininum of mininum of id3,test code
> here:
> create table #t
> (
> ID1 int,
> ID2 int,
> ID3 int
> )
>
> insert into #t
> select 1005, 1, 37
> union select 1005, 2, 0
> union select 1005, 3, 0
> union select 1005, 4, 54
> union select 1005, 5, 38
> union select 1007, 1, 2
> union select 1009, 1, 1
> union select 1009, 2, 3
> union select 1010, 1, 1
> union select 1010, 2, 1
> union select 1010, 3, 2
> union select 1010, 4, 3
>
> select * from #t
>
> select id1,mid2=min(id2),mmid3 = min(mid3) from
> (
> select id1,id2,mid3=min(id3) from #t group by id1,id2 --order by id1
> ) as n1
> group by id1
>
> drop table #t

That was a quick response! It yields:

1005, 1, 0
1007, 1, 2
1009, 1, 1
1010, 1, 1

which is nearly what I was looking for:

1005, 2, 0
1007, 1, 2
1009, 1, 1
1010, 1, 1

A min(id3) for a give min(id2) for a given id1 with the same record
contents associated (i.e. not 1005, 1, 0). Am I asking for the
impossible as I am still scratching my head. I think it comes down to
a nested "top 1" nested under another "top 1" or a couple nested
subqueries with "min()" or such.

Thanks again,
rebump

xyb

3/20/2007 3:25:00 AM

0

On 3?20?, ??11?14?, "rebump" <reb...@gmail.com> wrote:
> On Mar 19, 10:06 pm, "xyb" <xiangyua...@gmail.com> wrote:
>
>
>
>
>
> > On 3?20?, ??10?37?, "rebump" <reb...@gmail.com> wrote:> I should add to the above...SQL Server 2000 (i.e. no 2005 :( )
>
> > > Thanks again,
> > > rebump
>
> > the main conception is to count mininum of mininum of id3,test code
> > here:
> > create table #t
> > (
> > ID1 int,
> > ID2 int,
> > ID3 int
> > )
>
> > insert into #t
> > select 1005, 1, 37
> > union select 1005, 2, 0
> > union select 1005, 3, 0
> > union select 1005, 4, 54
> > union select 1005, 5, 38
> > union select 1007, 1, 2
> > union select 1009, 1, 1
> > union select 1009, 2, 3
> > union select 1010, 1, 1
> > union select 1010, 2, 1
> > union select 1010, 3, 2
> > union select 1010, 4, 3
>
> > select * from #t
>
> > select id1,mid2=min(id2),mmid3 = min(mid3) from
> > (
> > select id1,id2,mid3=min(id3) from #t group by id1,id2 --order by id1
> > ) as n1
> > group by id1
>
> > drop table #t
>
> That was a quick response! It yields:
>
> 1005, 1, 0
> 1007, 1, 2
> 1009, 1, 1
> 1010, 1, 1
>
> which is nearly what I was looking for:
>
> 1005, 2, 0
> 1007, 1, 2
> 1009, 1, 1
> 1010, 1, 1
>
> A min(id3) for a give min(id2) for a given id1 with the same record
> contents associated (i.e. not 1005, 1, 0). Am I asking for the
> impossible as I am still scratching my head. I think it comes down to
> a nested "top 1" nested under another "top 1" or a couple nested
> subqueries with "min()" or such.
>
> Thanks again,
> rebump- ??????? -
>
> - ??????? -

e:)
select a.id1,mid2=min(id2),a.mid3 from
(
select id1,id2,mid3=min(id3) from #t group by id1,id2
) a join
(
select id1,mmid3= min(mid3) from
(
select id1,id2,mid3=min(id3) from #t group by id1,id2-- order by id1
) as n1
group by id1
) b
on a.id1=b.id1 and a.mid3 = b.mmid3
group by a.id1,a.mid3

rebump

3/20/2007 3:28:00 AM

0

On Mar 19, 10:25 pm, "xyb" <xiangyua...@gmail.com> wrote:
> On 3?20?, ??11?14?, "rebump" <reb...@gmail.com> wrote:
>
>
>
>
>
> > On Mar 19, 10:06 pm, "xyb" <xiangyua...@gmail.com> wrote:
>
> > > On 3?20?, ??10?37?, "rebump" <reb...@gmail.com> wrote:> I should add to the above...SQL Server 2000 (i.e. no 2005 :( )
>
> > > > Thanks again,
> > > > rebump
>
> > > the main conception is to count mininum of mininum of id3,test code
> > > here:
> > > create table #t
> > > (
> > > ID1 int,
> > > ID2 int,
> > > ID3 int
> > > )
>
> > > insert into #t
> > > select 1005, 1, 37
> > > union select 1005, 2, 0
> > > union select 1005, 3, 0
> > > union select 1005, 4, 54
> > > union select 1005, 5, 38
> > > union select 1007, 1, 2
> > > union select 1009, 1, 1
> > > union select 1009, 2, 3
> > > union select 1010, 1, 1
> > > union select 1010, 2, 1
> > > union select 1010, 3, 2
> > > union select 1010, 4, 3
>
> > > select * from #t
>
> > > select id1,mid2=min(id2),mmid3 = min(mid3) from
> > > (
> > > select id1,id2,mid3=min(id3) from #t group by id1,id2 --order by id1
> > > ) as n1
> > > group by id1
>
> > > drop table #t
>
> > That was a quick response!  It yields:
>
> > 1005, 1, 0
> > 1007, 1, 2
> > 1009, 1, 1
> > 1010, 1, 1
>
> > which is nearly what I was looking for:
>
> > 1005, 2, 0
> > 1007, 1, 2
> > 1009, 1, 1
> > 1010, 1, 1
>
> > A min(id3) for a give min(id2) for a given id1 with the same record
> > contents associated (i.e. not 1005, 1, 0).  Am I asking for the
> > impossible as I am still scratching my head.  I think it comes down to
> > a nested "top 1" nested under another "top 1" or a couple nested
> > subqueries with "min()" or such.
>
> > Thanks again,
> > rebump- ??????? -
>
> > - ??????? -
>
> e:)
> select a.id1,mid2=min(id2),a.mid3 from
> (
> select id1,id2,mid3=min(id3) from #t group by id1,id2
> ) a join
> (
>         select id1,mmid3= min(mid3) from
>         (
>         select id1,id2,mid3=min(id3) from #t group by id1,id2-- order by id1
>         ) as n1
>         group by id1
> ) b
> on a.id1=b.id1 and a.mid3 = b.mmid3
> group by a.id1,a.mid3- Hide quoted text -
>
> - Show quoted text -

Holy mackeral!!! You're the winner kid! May the force be with you!

Thanks, I appreciate it greatly. I have to study you solution now.

Humbly yours,
rebump

rebump

3/20/2007 3:34:00 AM

0

On Mar 19, 10:25 pm, "xyb" <xiangyua...@gmail.com> wrote:
> On 3?20?, ??11?14?, "rebump" <reb...@gmail.com> wrote:
>
>
>
>
>
> > On Mar 19, 10:06 pm, "xyb" <xiangyua...@gmail.com> wrote:
>
> > > On 3?20?, ??10?37?, "rebump" <reb...@gmail.com> wrote:> I should add to the above...SQL Server 2000 (i.e. no 2005 :( )
>
> > > > Thanks again,
> > > > rebump
>
> > > the main conception is to count mininum of mininum of id3,test code
> > > here:
> > > create table #t
> > > (
> > > ID1 int,
> > > ID2 int,
> > > ID3 int
> > > )
>
> > > insert into #t
> > > select 1005, 1, 37
> > > union select 1005, 2, 0
> > > union select 1005, 3, 0
> > > union select 1005, 4, 54
> > > union select 1005, 5, 38
> > > union select 1007, 1, 2
> > > union select 1009, 1, 1
> > > union select 1009, 2, 3
> > > union select 1010, 1, 1
> > > union select 1010, 2, 1
> > > union select 1010, 3, 2
> > > union select 1010, 4, 3
>
> > > select * from #t
>
> > > select id1,mid2=min(id2),mmid3 = min(mid3) from
> > > (
> > > select id1,id2,mid3=min(id3) from #t group by id1,id2 --order by id1
> > > ) as n1
> > > group by id1
>
> > > drop table #t
>
> > That was a quick response!  It yields:
>
> > 1005, 1, 0
> > 1007, 1, 2
> > 1009, 1, 1
> > 1010, 1, 1
>
> > which is nearly what I was looking for:
>
> > 1005, 2, 0
> > 1007, 1, 2
> > 1009, 1, 1
> > 1010, 1, 1
>
> > A min(id3) for a give min(id2) for a given id1 with the same record
> > contents associated (i.e. not 1005, 1, 0).  Am I asking for the
> > impossible as I am still scratching my head.  I think it comes down to
> > a nested "top 1" nested under another "top 1" or a couple nested
> > subqueries with "min()" or such.
>
> > Thanks again,
> > rebump- ??????? -
>
> > - ??????? -
>
> e:)
> select a.id1,mid2=min(id2),a.mid3 from
> (
> select id1,id2,mid3=min(id3) from #t group by id1,id2
> ) a join
> (
>         select id1,mmid3= min(mid3) from
>         (
>         select id1,id2,mid3=min(id3) from #t group by id1,id2-- order by id1
>         ) as n1
>         group by id1
> ) b
> on a.id1=b.id1 and a.mid3 = b.mmid3
> group by a.id1,a.mid3- Hide quoted text -
>
> - Show quoted text -

Just for giggles...is there an easier way to do this in SQL Server
2005 (if we ever upgrade)? Like with "apply" or such?

Thanks again...you rawk!
rebump

Kent Tegels

3/20/2007 3:48:00 AM

0

Hello rebump,

> Just for giggles...is there an easier way to do this in SQL Server
> 2005 (if we ever upgrade)? Like with "apply" or such?

with rs as (select id1,id2,id3,row_number() over (partition by id1 order
by id3,id2) as r from t)
select id1,id2,id3 from rs where r = 1

Thanks!
Kent Tegels
DevelopMentor
http://staff.develop.co...

rebump

3/20/2007 3:58:00 AM

0

On Mar 19, 10:47 pm, Kent Tegels <kteg...@develop.com> wrote:
> Hello rebump,
>
> > Just for giggles...is there an easier way to do this in SQL Server
> > 2005 (if we ever upgrade)? Like with "apply" or such?
>
> with rs as (select id1,id2,id3,row_number() over (partition by id1 order
> by id3,id2) as r from t)
> select id1,id2,id3 from rs where r = 1
>
> Thanks!
> Kent Tegels
> DevelopMentorhttp://staff.develop.co...

Wow. What a night for quick responses. I will take your word that
that works on SQL 2005. I thought having it in the thread would
benefit others searching for a solution to this sort of thing since I
didn't really find one out there already for 2000 (or 2005 but I did
see something similar with "apply" and only two levels deep instead of
three levels deep). I may have to install 2005 to check out all the
groovy new features.

Thanks again y'all,
rebump

Uri Dimant

3/20/2007 5:16:00 AM

0

Hi
Based on xyb DDL and using SQL Server 2000

select id1,min(id2) rr,id3 from #t
where id3 =(select min(id3) from #t t where t.id1=#t.id1 )
group by id1,id3


"rebump" <rebump@gmail.com> wrote in message
news:1174363074.063415.11690@n59g2000hsh.googlegroups.com...
> On Mar 19, 10:47 pm, Kent Tegels <kteg...@develop.com> wrote:
>> Hello rebump,
>>
>> > Just for giggles...is there an easier way to do this in SQL Server
>> > 2005 (if we ever upgrade)? Like with "apply" or such?
>>
>> with rs as (select id1,id2,id3,row_number() over (partition by id1 order
>> by id3,id2) as r from t)
>> select id1,id2,id3 from rs where r = 1
>>
>> Thanks!
>> Kent Tegels
>> DevelopMentorhttp://staff.develop.co...
>
> Wow. What a night for quick responses. I will take your word that
> that works on SQL 2005. I thought having it in the thread would
> benefit others searching for a solution to this sort of thing since I
> didn't really find one out there already for 2000 (or 2005 but I did
> see something similar with "apply" and only two levels deep instead of
> three levels deep). I may have to install 2005 to check out all the
> groovy new features.
>
> Thanks again y'all,
> rebump
>


rebump

3/20/2007 4:37:00 PM

0

On Mar 20, 12:16 am, "Uri Dimant" <u...@iscar.co.il> wrote:
> Hi
> Based on xyb DDL and using SQL Server 2000
>
> select id1,min(id2) rr,id3 from #t
> where id3 =(select min(id3) from #t t where t.id1=#t.id1 )
> group by id1,id3
>
> "rebump" <reb...@gmail.com> wrote in message
>
> news:1174363074.063415.11690@n59g2000hsh.googlegroups.com...
>
>
>
> > On Mar 19, 10:47 pm, Kent Tegels <kteg...@develop.com> wrote:
> >> Hello rebump,
>
> >> > Just for giggles...is there an easier way to do this in SQL Server
> >> > 2005 (if we ever upgrade)? Like with "apply" or such?
>
> >> with rs as (select id1,id2,id3,row_number() over (partition by id1 order
> >> by id3,id2) as r from t)
> >> select id1,id2,id3 from rs where r = 1
>
> >> Thanks!
> >> Kent Tegels
> >> DevelopMentorhttp://staff.develop.co...
>
> > Wow. What a night for quick responses. I will take your word that
> > that works on SQL 2005. I thought having it in the thread would
> > benefit others searching for a solution to this sort of thing since I
> > didn't really find one out there already for 2000 (or 2005 but I did
> > see something similar with "apply" and only two levels deep instead of
> > three levels deep). I may have to install 2005 to check out all the
> > groovy new features.
>
> > Thanks again y'all,
> > rebump- Hide quoted text -
>
> - Show quoted text -

I likey. I likey alot! Thanks for an even more condensed method.

rebump