get two min value for each group! help me

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 2005 Forums

Transact-SQL (2005)

get two min value for each group! help me

Author

Topic

txk2601
Starting Member

19 Posts

Posted - 2009-05-25 : 05:51:48

Hi all.
I have Table by following structure:

CREATE table tblProducts
(
[Name] varchar(50) primary key,
Loc varchar(20) primary key,
Number int
)
INSERT INTO tblProducts
SELECT 'ITEMA', 'LOCA', 2
UNION ALL
SELECT 'ITEMA', 'LOCB', 4
UNION ALL
SELECT 'ITEMA', 'LOCC', 10
UNION ALL
SELECT 'ITEMB', 'LOCD', 20
UNION ALL
SELECT 'ITEMB', 'LOCE', 14
UNION ALL
SELECT 'ITEMB', 'LOCF', 10
UNION ALL
SELECT 'ITEMC', 'LOCG', 4
UNION ALL
SELECT 'ITEMC', 'LOCH', 16
UNION ALL
SELECT 'ITEMC', 'LOCI', 12

-- I wan't get 2 min value by number of each group by Nam. Please help me. Thanks alot!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-25 : 05:55:45

select name,loc,number from (
select row_number()over(partition by name order by name)as rid,
* from tblProducts)s
where rid<=2

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-25 : 05:56:14

select name, number from
(select name, Number, z_rank = row_number() over (partition by name order by Number)
from tblProducts
) a
where z_rank <= 2

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-25 : 05:56:40

[code]
select *
from
(
select *, row_no = row_number () over (partition by [Name] order by Number)
from tblProducts
) p
where row_no <= 2
[/code]

KH
[spoiler]Time is always against us[/spoiler]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-25 : 05:57:11

ur create table should be like this
CREATE table tblProducts
(
[Name] varchar(50) ,
Loc varchar(20) ,
Number int
primary key(name,loc)
)

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-25 : 05:57:18

x 2

KH
[spoiler]Time is always against us[/spoiler]

nr
SQLTeam MVY

12543 Posts

Posted - 2009-05-25 : 05:57:27

lol

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

txk2601
Starting Member

19 Posts

Posted - 2009-05-25 : 06:17:16

Thanks all alot!

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-25 : 06:18:03