Solved

Is there a way to select no more than 4 rows in a Select statement even when there is more than 4 that fits the Select?

Posted on 2008-10-17
6
174 Views
Last Modified: 2010-03-20
Hi, I have a tabel with 2 columns.  Is there a way to write a SELECT statement to select both columns form this table but limit the return result to no more than 4 rows when there could be more than 4 or less than 4 rows that fits the filter?  Thank you.
0
Comment
Question by:lapucca
6 Comments
 
LVL 6

Expert Comment

by:divyeshhdoshi
ID: 22739598
select top 4 col1, col2 from table name ;
0
 

Expert Comment

by:francasado
ID: 22739602
try this:

Select top 4 column1, column2 from table where...
0
 

Author Comment

by:lapucca
ID: 22739679
Sorry, didn't make my question clear.  I want to retrieve the top 4 rows of records that has the same col1 value and select them into a new table.  Is this possible?
col1                  col2
10                    a
10                    b
10                   c
10                    d
20                    x
20                     y

0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 22739783
this should do:;
SELECT col1, col2
  FROM ( select col1, col2, row_number() over(partition by col1 order by col2) r from yourtable ) as sq
 WHERE sq.r <= 4
 

Open in new window

0
 

Author Comment

by:lapucca
ID: 22739868
You are a genius.  Thank you very much!
0
 
LVL 6

Expert Comment

by:divyeshhdoshi
ID: 22739870
select * from
(
select col1, col2, Rank() over (partition by col1 order by col2) as rank from table
) tmp
where rank <=4
 
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now