• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 93
  • Last Modified:

Selecting specific rows

I need to select the Top row of the table and the bottom row of the table from the CUSTOMERORDER table not sure how to do it. I'm running SQL server 2012.
This is what i have so far.

select *, CompanyName, Phone, lastname, orderdate
from Customer
      inner join Employee
            on Customer.AcctRepNo = Employee.EmpNo
      inner join CustomerOrder
            on Customer.CustNo = CustomerOrder.CustNo
0
Rob Fried
Asked:
Rob Fried
  • 4
  • 3
  • 3
  • +2
2 Solutions
 
_agx_Commented:
What do you mean by top and bottom rows?  

Can you post a small sample of the raw data and the desired results?
0
 
Anjala Baby.Net DeveloperCommented:
Is it the first row you need to select?
0
 
Rob FriedAuthor Commented:
first and last row of the doc of the table posted below.
Doc13.docx
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Rob FriedAuthor Commented:
So orderno 10000 and 10032
0
 
Rgonzo1971Commented:
Hi,

pls try

Select * FROM (select Top 1 *, CompanyName, Phone, lastname, orderdate
 from Customer
       inner join Employee
             on Customer.AcctRepNo = Employee.EmpNo
       inner join CustomerOrder
             on Customer.CustNo = CustomerOrder.CustNo ORDER BY OrderNo Asc
Union
select Top 1 *, CompanyName, Phone, lastname, orderdate
 from Customer
       inner join Employee
             on Customer.AcctRepNo = Employee.EmpNo
       inner join CustomerOrder
             on Customer.CustNo = CustomerOrder.CustNo ORDER BY OrderNo Desc)

Open in new window

Regards
0
 
Rob FriedAuthor Commented:
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ')'.

I think it has something to do with the union.
0
 
_agx_Commented:
(no points ...)

Think you need to add an alias for the query the very end:
     
        Select * FROM ( .... etc..... )  tbl
0
 
Pawan KumarDatabase ExpertCommented:
Try this.. UNION ALL is required, UNION will decrease the performance. Adding on solution of <<Rgonzo1971>>

Select * FROM 
(

select Top 1 * , CompanyName, Phone, lastname, orderdate
 from Customer
       inner join Employee
             on Customer.AcctRepNo = Employee.EmpNo
       inner join CustomerOrder
             on Customer.CustNo = CustomerOrder.CustNo ORDER BY OrderNo Asc

UNION ALL

select Top 1 *, CompanyName, Phone, lastname, orderdate
 from Customer
       inner join Employee
             on Customer.AcctRepNo = Employee.EmpNo
       inner join CustomerOrder
             on Customer.CustNo = CustomerOrder.CustNo ORDER BY OrderNo Desc

)a

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
@Rob Fried..

If the question is answered then you have to select the answer and submit.
0
 
_agx_Commented:
BTW, in cases where one answer builds upon code from another, ie Rgonzo1971's answer, you can always accept multiple answers :-)
0
 
Pawan KumarDatabase ExpertCommented:
Agree with _agx_sir.

Dear Rob Fried - If multiple people help you on a problem then you have to award points to all those people. In this case _agx_ sir provide the initial Solution. So his solution should be the best solution. After this you can select assisted solutions if you want.

Could you please check if you can make the changes?
0
 
_agx_Commented:
Thanks. Just to clarify the initial answer was provided by Rgonzo1971 (not me) so any split should be with them  :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 4
  • 3
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now