Is there a better way? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Is there a better way?

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-24 : 10:44:38

Eder writes "Suppose table t is:

id type val text
-- ---- --- ----
 0    A   1    x
 1    B   2    y
 2    B  18    z
 3    C   7    w
 4    C  11    r

We need the lines where type in ('B','C'), with max(val):

id type val text
-- ---- --- ----
 2    B  18    z
 4    C  11    r

My attempt:
select a.* from t as a inner join
(select type, max(val) m from t where type in ('B','C') group by type) as b on a.type = b.type and a.val=b.m

Is there a better way?"

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-04-24 : 12:14:41

how bout...


select
    [id],
    type,
    max(val) as maxval,
    [text]
from
    EderTable
group by
    [id],
    type,
    [text]
having
    val in ('B','C')

(ID and TEXT are reserved words and thus poor choice for column names)

<O>

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2002-04-24 : 14:17:40

sorry page that won't work ... I thought about that for about 2 seconds and realized it would return the entire table ... the join works fine ... what i'm wondering is what if the table has rows that are the same except for the text column ... either way I'm sure the developer can overcome this...

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-04-24 : 14:42:53

Haven't tested it (since you haven't posted the CREATE TABLE and INSERT statements):

SELECT o.type, (SELECT MAX(i.Val) FROM t i WHERE i.type = o.type) AS Val
FROM t o
WHERE o.type IN ('B', 'C')
GO

--
HTH,
Vyas
Check out my SQL Server site @
http://vyaskn.tripod.com

VyasKN
SQL Server MVP & SQLTeam MVY

313 Posts

Posted - 2002-04-24 : 14:46:26

quote:

SELECT o.type, (SELECT MAX(i.Val) FROM t i WHERE i.type = o.type) AS Val
FROM t o
WHERE o.type IN ('B', 'C')
GO

Just realized, you'll need a DISTINCT there...

--
HTH,
Vyas
Check out my SQL Server site @
http://vyaskn.tripod.com

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-04-24 : 15:19:49

quote:

sorry page that won't work ... I thought about that for about 2 seconds and realized it would return the entire table ... the join works fine ... what i'm wondering is what if the table has rows that are the same except for the text column ... either way I'm sure the developer can overcome this...

You are correct. I was wrong . . . allow me to take another stab.

Using the following as ddl . . .


create table #edertable (
    [id] int,
    type char(1),
    val  int,
    [text] char(1) )

insert #edertable values (0,'a',1,'x')
insert #edertable values (1,'b',2,'y')
insert #edertable values (2,'b',18,'z')
insert #edertable values (3,'c',7,'w')
insert #edertable values (4,'c',11,'r')

... I think I was able to come up with a query that gives slightly better performance. This will be greatly effect by indexes, as my experiments have none.

The original posters query (re-written to supress my pathological need to format things) looks like this . . .


select
    b.[id],
    b.type,
    a.maxval,
    b.[text]
from
   (select
        type,
        max(val) as maxval
    from
        #EderTable
    where
        type in ('B','C')
    group by 
        type ) a
    inner join #EderTable b
        on (a.type = b.type and
            a.maxval = b.val)

... and presents with a subtree cost of 0.103. My query looks like this . . .


select
    [id],
    type,
    val,
    [text]
from
    #EderTable a
where
    type in ('b','c') and
    not exists (
        select
            1
        from
            #EderTable b
        where
            a.type = b.type and
            a.val < b.val )

... and enjoys a subtree cost of 0.0758.

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources