Row number issue

Question

Hi,
Is there one example for that I want to ensure only one row is returned for the select statement?

Answer 1

Check one example:

 select top(1) * from MyTable

Usually you will also use order by to return the first or the last row from a table.

Answer 2

Hi @Scott Huang

I want to ensure only one row is returned for the select statement

You can use TOP(1) to select only one row of data and edit the 'order by' clause according to different needs.
Check this example:

CREATE TABLE ForgeRock ([productName] varchar(13), [description] varchar(57), [amount] INT);  
  
INSERT INTO ForgeRock VALUES  
('OpenIDM', 'Platform for building enterprise provisioning solutions',100),  
('OpenAM', 'Full-featured access management',200),  
('OpenDJ', 'Robust LDAP server for Java',300);  
  
--Select one row that has the least amount  
SELECT TOP(1)* FROM ForgeRock  
ORDER BY amount ASC;  
  
--Select one row that has the biggest amount  
SELECT TOP(1)* FROM ForgeRock  
ORDER BY amount DESC;  
  
--select Random One row in a table  
SELECT TOP(1)* FROM ForgeRock  
ORDER BY NewId();  

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 3

You can use a row_number windown function to define your first row and retrieve it.
Here are some examples:
select * from (
select *, row_number()Over(order by somecolumn asc) rn
from yourtable) t
where rn=1

--or  
  
select * from (  
select *, row_number()Over(order by somecolumn  desc)  rn   
from yourtable) t  
where rn=1