SELECTing a sample

Question

Post reply

SELECTing a sample

dlongnecker-802303

Ten Centuries

Points: 1280
More actions
July 21, 2008 at 7:45 am

#191196

Hey guys, I have a table like this:
DECLARE @mytable TABLE (
field1 VARCHAR(10),
GroupKey INT)
Where the GroupKey is a FK referencing a lookup table (there are many records in @mytable for every record in the foreign table)
How can I return, say, the top 2 records from @mytable for every ForeignKey. Example:
INSERT @mytable
select 'test1', 1 UNION ALL
select 'test2', 1 UNION ALL
select 'test3', 1 UNION ALL
select 'test4', 2 UNION ALL
select 'test5', 2 UNION ALL
select 'test6', 2
And I'm trying to build a query to return
field1 ForeignKey
-------- ------------
test1 1
test2 1
test4 2
test5 2
---
Dlongnecker

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

RyanRandall SSChampion Points: 13623 More actions · Answer 1

Here's one way...

select * from @mytable a where 2 >

(select count(*) from @mytable where GroupKey = a.GroupKey and field1 < a.field1)

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

dlongnecker-802303 Ten Centuries Points: 1280 More actions · Answer 2

Sweet, thanks.

That should work because i'm doing this in a small database with few users. But is there a way to do it that isn't a triangular join?

---
Dlongnecker

RyanRandall SSChampion Points: 13623 More actions · Answer 3

But is there a way to do it that isn't a triangular join?

Only by upgrading to 2005...

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

dlongnecker (7/21/2008)
Sweet, thanks.
That should work because i'm doing this in a small database with few users. But is there a way to do it that isn't a triangular join?

Like this:

Select *, identity(int) as [ID]

into #tempTable

From @mytable

Order by GroupKey, field1

Select Min(field1), GroupKey

From #tempTable

Group by GroupKey

UNION

Select field1, GroupKey

From #tempTable

Where ID In(Select Min(ID)+1

From #tempTable

Group by GroupKey

)

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

dlongnecker-802303 Ten Centuries Points: 1280 More actions · Answer 5

Cool. Thanks.

I was hoping there would be some nicer solutions, but these will do!

---
Dlongnecker

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 6

dlongnecker (7/21/2008)
I was hoping there would be some nicer solutions, but these will do!

Heh... I'm thinking the folks that were good enough to help, so far, may take offense to that. 😉

Here's one that uses no temp table and no triangular join...

DECLARE @mytable TABLE (

field1 VARCHAR(10),

GroupKey INT)

INSERT @mytable

select 'test1', 1 UNION ALL

select 'test2', 1 UNION ALL

select 'test3', 1 UNION ALL

select 'test4', 2 UNION ALL

select 'test5', 2 UNION ALL

select 'test6', 2

SELECT *

FROM @MyTable mt1

WHERE Field1 IN (SELECT TOP 2 Field1

FROM @MyTable mt2

WHERE mt2.GroupKey = mt1.GroupKey)

Even though that one will be pretty fast, if you have millions of rows to do this to, you may want to consider "Ranking" which can be done in SQL Server 2000 using the techniques found in the following article instead of using a correlated subquery...

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 7

Also, I gotta know because this is not a run of the mill request... I've only seen one other time when this was needed... a customer could have two identical "things" as part of a dupe check... but they weren't allowed to have more than two. What are you using this for?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

dlongnecker-802303 Ten Centuries Points: 1280 More actions · Answer 8

I'm an intern that's working on a database for internal use that documents various aspects of a business. Some of the data collection is automated, but some of it is held in the heads of the employees and we're trying to pull it and get it in a database. There was also partial information available when I began.

I have the database generate a list of missing or outdated data that needs to be updated, and I found that when a user is presented with 150 pieces of information that needs updating, they get overwhelmed and simply ignore it. This is @mytable as I referenced it. The FK catagorizes the record by type of data error.

Instead of showing all 150 records, I wanted to sample just a few pieces of information from every type of error. If a user is shown just a few records that need updating I found they're more likely to look at them and attempt to update the data rather than simply ignoring them all.

---
Dlongnecker

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 9

Perfect and good idea. Thanks for sharing the background of this problem. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Greg Snidow SSCoach Points: 16108 More actions · Answer 10

Sounds like my company. We have so many people who absolutely refuse to make all our jobs easier by using the great tools we've been given, like a database.

Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

dlongnecker (7/21/2008)
I was hoping there would be some nicer solutions, but these will do!

Heh. My wife said almost exactly the same thing when I proposed to her. 😛

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 12

Heh... now that there's some funny stuff Mr. Young! 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Venkatesan Prabu SSCommitted Points: 1892 More actions · Answer 13

Nice to join with you all in answering this question,

Select field1,GroupKey from

(

select row_number() over (partition by GroupKey order by GroupKey) as rownumber , field1,GroupKey from @mytable

)tbl

where tbl.rownumber<=2

The above query will fetch your output

Thanks and Regards,
Venkatesan Prabu, 😛
My Blog:

http://venkattechnicalblog.blogspot.com/

RyanRandall SSChampion Points: 13623 More actions · Answer 14

Venkatesan Prabu (7/27/2008)
Nice to join with you all in answering this question,
Select field1,GroupKey from
(
select row_number() over (partition by GroupKey order by GroupKey) as rownumber , field1,GroupKey from @mytable
)tbl
where tbl.rownumber<=2
The above query will fetch your output

This won't work in SQL 2000, I'm afraid Venkatesan. Look out for which forums posts are in!

Ryan Randall

Solutions are easy. Understanding the problem, now, that's the hard part.