Get some data from table plus only N rows for each

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

Transact-SQL (2000)

Get some data from table plus only N rows for each

Author

Topic

rity
Starting Member

4 Posts

Posted - 2005-10-25 : 07:57:05

I want to get rows from table (with all columns) which is number of occurrences less then N rows and for each group that occurs more then N rows only first N rows. I had a question in this forum a while a go, but I could not implement that solution in my case, sorry I'm beginner in SQL.

I have table like this:

Declare @tbldata table
(
[gr] [char] (4) NOT NULL ,
[pgr] [char] (2) NOT NULL ,
[okv] [char] (1) NOT NULL ,
[phone] [nvarchar] (100) NULL ,
[nri] [int] NULL ,
[text] [nvarchar] (500) NULL ,
[apic] [nvarchar] (20) NULL DEFAULT (''),
[order] [int] NULL DEFAULT (0)
)

insert into @tblData
Select '010', 'A', 1, '555-5632', 1, 'some texta', 'PB002', 1 union
Select '010', 'A', 1, '555-5632', 1, 'some textb', 'PB002', 1 union
Select '010', 'A', 1, '555-5632', 3, 'some texts', 'PB002', 1 union
Select '010', 'A', 1, '555-5632', 1, 'some texts', 'PB002', 1 union
Select '010', 'A', 1, '555-5632', 1, 'some textd', 'PB003', 1 union
Select '020', 'b', 1, '555-5632', 1, 'some textf', 'PB004', 1 union
Select '020', 'b', 1, '555-5633', 1, 'some text1', 'PB004', 1 union
Select '020', 'b', 1, '555-5635', 1, 'some text2', 'PB004', 1 union
Select '020', 'b', 1, '555-5633', 2, 'some text3', 'PB004', 1 union
Select '030', 'A', 1, '555-5634', 1, 'some text4', 'PB005', 1 union
Select '040', 'A', 1, '555-5634', 1, 'some text', 'PB006', 1 union
Select '050', 'C', 1, '555-5636', 2, 'some text', 'PB007', 1 union
Select '050', 'C', 1, '555-5636', 1, 'some text', 'PB007', 1 union
Select '060', 'A', 1, '555-5634', 1, 'some text', 'PB008', 1

select left(phone, 15) as phone, count(*) as nbr from @tblData where nri = 1 group by phone having count(*) >= 3 order by nbr desc

phone nbr
--------------- -----------
555-5632 5
555-5634 3

Now, I need all the data from my table where phone number is different from these numbers in previous sql querry and only in this case two (N) rows for each number(s) from previous querry.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-25 : 08:07:43

Is this?

Select * from @tbldata T where not exists(
select left(phone, 15) as phone from @tblData where nri = 1 and phone=T.phone group by phone having count(*) >= 3)

Madhivanan

Failing to plan is Planning to fail

rity
Starting Member

4 Posts

Posted - 2005-10-25 : 08:19:31

quote:
Originally posted by madhivanan

Is this?

Select * from @tbldata T where not exists(
select left(phone, 15) as phone from @tblData where nri = 1 and phone=T.phone group by phone having count(*) >= 3)

Madhivanan

Failing to plan is Planning to fail

Well, this is part of it, you didn't take any row for phones which occurs more then N times. I need to take N rows of them and ignore rest of it.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-25 : 08:41:01

Post the result you want

Madhivanan

Failing to plan is Planning to fail

rity
Starting Member

4 Posts

Posted - 2005-10-25 : 09:02:08

quote:
Originally posted by madhivanan

Post the result you want

Madhivanan

Failing to plan is Planning to fail

gr pgr okv nri apic ord
---- ---- ---- -------------------- ----------- -------------------- -------------------- -----------
010 A 1 555-5632 1 some texta PB002 1
010 A 1 555-5632 1 some textb PB002 1
010 A 1 555-5632 1 some textd PB003 1
010 A 1 555-5632 1 some texts PB002 1
020 b 1 555-5632 1 some textf PB004 1
020 b 1 555-5633 1 some text1 PB004 1
020 b 1 555-5633 1 some text3 PB004 1
030 A 1 555-5634 1 some text4 PB005 1
040 A 1 555-5634 1 some text PB006 1
060 A 1 555-5634 1 some text PB008 1
020 b 1 555-5635 1 some text2 PB004 1
050 C 1 555-5636 1 some text PB007 1

(12 row(s) affected)

I have this phone 5 times 555-5632, and this 555-5634 phone 3 times
Now I will take only two rows for this phone 555-5632, and two for this 555-5634, and rest of rows which occurs less then 3 times.

gr pgr okv nri apic ord
---- ---- ---- -------------------- ----------- -------------------- -------------------- -----------
010 A 1 555-5632 1 some texta PB002 1
010 A 1 555-5632 1 some textb PB002 1
030 A 1 555-5634 1 some text4 PB005 1
040 A 1 555-5634 1 some text PB006 1
020 b 1 555-5633 1 some text1 PB004 1
020 b 1 555-5633 1 some text3 PB004 1
020 b 1 555-5635 1 some text2 PB004 1
050 C 1 555-5636 1 some text PB007 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-25 : 09:05:56

Refer point 2 here
http://weblogs.sqlteam.com/mladenp/archive/2005/08/01/7421.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources