Clash :Min() vs Max()

Question

Post reply

Clash :Min() vs Max()

karthik M

One Orange Chip

Points: 29139
More actions
April 30, 2008 at 7:32 am

#71937

Dear All,
I have one senario.
Create table #t1
(
PRID varchar(5),
Month int,
Year int
)
insert into #t1
select 'AAA',5,2006
union all
select 'AAA',1,2004
union all
select 'AAA',12,2002
union all
select 'AAA',2,2008
union all
select 'BBB',3,2008
union all
select 'BBB',3,2007
union all
select 'BBB',1,2007
Expected output :
PRID MIN(Year) MIN(Year)
AAA 2002 12
BBB 2007 1
I used the following query to do the above one.
I got the output,but i am using # table, i want to avoid it also i want to do it in a single query.
My Query:
select PRID,Min(Year) as Y
into #A1
from #t1
group by PRID
select #A1.PRID,Y,Min(Month)
from #A1,#t1
where #A1.PRID = #t1.PRID
and #A1.Y = #t1.Year
group by #A1.PRID,Y
Inputs are welcome !
karthik

Viewing 9 posts - 1 through 9 (of 9 total)

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

RyanRandall SSChampion Points: 13623 More actions · Answer 1

For a start, you can just take what you've done and nest the query you were putting into #A1

select #A1.PRID,Y,Min(Month)

from (

select PRID,Min(Year) as Y

from #t1

group by PRID) #A1,#t1

where #A1.PRID = #t1.PRID

and #A1.Y = #t1.Year

group by #A1.PRID,Y

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall SSChampion Points: 13623 More actions · Answer 2

Here's another way...

select PRID, YearAndMonth / 100 as Year, YearAndMonth % 100 as Month

from (select PRID, min(Year * 100 + Month) as YearAndMonth from #t1 group by PRID) a

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

karthik M One Orange Chip Points: 29139 More actions · Answer 3

Ryan,

Wow!

select PRID, min(Year * 100 + Month) as YearAndMonth from #t1 group by PRID

Can you explain me the above one ? It is different.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Ryan,

How the minimum Month value is added exactly with year ? Kindly explain me.

If i execute like

Select PRID,Min(Year),Min(Month)

from #t1

group by PRID

it gives us the wrong output.

AAA 2002 1 -- HERE is the wrong.Actually it has to display 12

BBB 2007 1

But your query gave me

AAA 200212

BBB 200701

How ?

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 5

I have modified your query little

select PRID, min(Year * 100 + Month)/100 as Year,

min(Year * 100 + Month)%100 as Month,

From #t1 group by PRID

It is working perfectly.

karthik

RyanRandall SSChampion Points: 13623 More actions · Answer 6

This query...

select *, Year * 100 + Month as YearAndMonth from #t1

...gives this output...

/*

PRID Month Year YearAndMonth

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

AAA 5 2006 200605

AAA 1 2004 200401

AAA 12 2002 200212

AAA 2 2008 200802

BBB 3 2008 200803

BBB 3 2007 200703

BBB 1 2007 200701

*/

And the YearAndMonth integer is so much easier to work with than 2 separate integers representing year and month.

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall SSChampion Points: 13623 More actions · Answer 7

karthikeyan (4/30/2008)
I have modified your query little
select PRID, min(Year * 100 + Month)/100 as Year,
min(Year * 100 + Month)%100 as Month,
From #t1 group by PRID
It is working perfectly.

Yep - that will work too. 🙂

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

karthik M One Orange Chip Points: 29139 More actions · Answer 8

karthik M

One Orange Chip

Points: 29139

April 30, 2008 at 9:10 am

#809125

Ryan,

Thanks a lot.

karthik