March 14, 2007 at 11:58 am
I have a table like:
CREATE TABLE [mytable] (
[mytable_pk] [int] NULL ,
[col1] [int] (50),
[goods] [varchar] (50),
[maxval] [int] NULL
)
it has some data like (just for example)
1, values1, banana, 100
2, values2, banana, 50
3, values3, apple, 10
5, values4, apple, 5
... and so on
I want to extract max values for maxval groupped by goods, in this case I'll have:
1, values1, banana, 100
3, values3, apple, 10
March 14, 2007 at 12:21 pm
SELECT OuterQuery.mytable_pk, OuterQuery.col1, OuterQuery.goods, OuterQuery.maxval
FROM mytable OuterQuery
INNER JOIN
( SELECT goods, max(maxval) as maxval
FROM mytable
GROUP BY goods
) InnerQuery
ON OuterQuery.goods = InnerQuery.goods
AND OuterQuery.maxval = InnerQuery.maxval
March 15, 2007 at 3:40 pm
Thank for useful suggestion
March 16, 2007 at 9:44 am
Since this is a SQL Server 2005 forum, try this
SELECT
mytable_pk,
col1,
goods
,
maxval
FROM
(
SELECT *,
ROW_NUMBER() OVER (PARTITION BY Goods ORDER BY MaxVal DESC) AS RecID
FROM MyTable
) AS x
WHERE
RecID = 1
N 56°04'39.16"
E 12°55'05.25"
March 16, 2007 at 2:30 pm
thank
very useful for sql 2k5
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply