how to select max amount rows

[Eric]

please help to select these rows from these tables my tables are

table1
table1Id groupId table2id price
1 1 1 10
2 1 3 1000
3 1 4 500
4 2 1 5
5 2 3 1000
6 2 2 2000

table2
table2id name
1 hello
2 test
3 test1
4 test2

ok i want to select maximum priced row from table1 grouped by groupid
with table2id and table2.name
my out put is

groupid price table2id table2.name
1 1000 3 test1
2 2000 2 test

if i do :
select groupid,max(tab le1.price) as price from table1 group by price

this will give me the max priced row from table1
but when i join them with the table2 it gives me all rows
like

Select groupid,max(pri ce) as price,table2id, table2.name from table1
inner join on table2
group by groupid,table2i d,table2.name

it gives me all rows cause i had to group by table2.id and table2.name
but i can't take it out cause it give me no aggrigated value error
i can't figure out any other way, please help

SQL Server 2000

thanks

eric

[Dan Guzman]

Re: how to select max amount rows

One method:

CREATE TABLE table1
(
table1Id int,
groupId int,
table2id int,
price int
)

INSERT INTO table1 VALUES(1, 1, 1, 10)
INSERT INTO table1 VALUES(2, 1, 3, 1000)
INSERT INTO table1 VALUES(3, 1, 4, 500)
INSERT INTO table1 VALUES(4, 2, 1, 5)
INSERT INTO table1 VALUES(5, 2, 3, 1000)
INSERT INTO table1 VALUES(6, 2, 2, 2000)

CREATE TABLE table2
(
table2id int,
name varchar(30)
)

INSERT INTO table2 VALUES(1, 'hello')
INSERT INTO table2 VALUES(2, 'test')
INSERT INTO table2 VALUES(3, 'test1')
INSERT INTO table2 VALUES(4, 'test2')

SELECT
t1.groupId,
t1.price,
t2.table2id,
t2.name
FROM table1 t1
JOIN table2 t2 ON
t1.table2id = t2.table2id
WHERE price =
(
SELECT MAX(price)
FROM table1 a
WHERE a.groupid = t1.groupid
)
ORDER BY t1.groupid, t2.name

--
Hope this helps.

Dan Guzman
SQL Server MVP
"Eric" <eakmeemana@hot mail.com> wrote in message
news:a3298bbc.0 401041629.791cf 9e@posting.goog le.com...[color=blue]
> please help to select these rows from these tables my tables are
>
> table1
> table1Id groupId table2id price
> 1 1 1 10
> 2 1 3 1000
> 3 1 4 500
> 4 2 1 5
> 5 2 3 1000
> 6 2 2 2000
>
> table2
> table2id name
> 1 hello
> 2 test
> 3 test1
> 4 test2
>
> ok i want to select maximum priced row from table1 grouped by groupid
> with table2id and table2.name
> my out put is
>
> groupid price table2id table2.name
> 1 1000 3 test1
> 2 2000 2 test
>
> if i do :
> select groupid,max(tab le1.price) as price from table1 group by price
>
> this will give me the max priced row from table1
> but when i join them with the table2 it gives me all rows
> like
>
> Select groupid,max(pri ce) as price,table2id, table2.name from table1
> inner join on table2
> group by groupid,table2i d,table2.name
>
> it gives me all rows cause i had to group by table2.id and table2.name
> but i can't take it out cause it give me no aggrigated value error
> i can't figure out any other way, please help
>
> SQL Server 2000
>
> thanks
>
> eric[/color]

[Mystery Man]

Re: how to select max amount rows

eakmeemana@hotm ail.com (Eric) wrote in message news:<a3298bbc. 0401041629.791c f9e@posting.goo gle.com>...[color=blue]
> please help to select these rows from these tables my tables are
>
> table1
> table1Id groupId table2id price
> 1 1 1 10
> 2 1 3 1000
> 3 1 4 500
> 4 2 1 5
> 5 2 3 1000
> 6 2 2 2000
>
> table2
> table2id name
> 1 hello
> 2 test
> 3 test1
> 4 test2
>
> ok i want to select maximum priced row from table1 grouped by groupid
> with table2id and table2.name
> my out put is
>
> groupid price table2id table2.name
> 1 1000 3 test1
> 2 2000 2 test
>
> if i do :
> select groupid,max(tab le1.price) as price from table1 group by price
>
> this will give me the max priced row from table1
> but when i join them with the table2 it gives me all rows
> like
>
> Select groupid,max(pri ce) as price,table2id, table2.name from table1
> inner join on table2
> group by groupid,table2i d,table2.name
>
> it gives me all rows cause i had to group by table2.id and table2.name
> but i can't take it out cause it give me no aggrigated value error
> i can't figure out any other way, please help
>
> SQL Server 2000
>
> thanks
>
> eric[/color]

Try the following

SELECT
t1.groupId
,t1.price
,t2.table2id
,t2.name
FROM
table1 t1
,table2 t2
WHERE
t1.table2id = t2.table2id
and price =(SELECT MAX(price) FROM table1 t1b WHERE t1b.groupId = t1.groupId)
ORDER BY
t1.groupId
,t2.name