Solved

Selecting specific rows

Posted on 2016-11-04
14
58 Views
Last Modified: 2016-11-06
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
Comment
Question by:Rob Fried
  • 4
  • 3
  • 3
  • +2
14 Comments
 
LVL 52

Expert Comment

by:_agx_
ID: 41873947
What do you mean by top and bottom rows?  

Can you post a small sample of the raw data and the desired results?
0
 

Expert Comment

by:Anjala Baby
ID: 41873962
Is it the first row you need to select?
0
 

Author Comment

by:Rob Fried
ID: 41874012
first and last row of the doc of the table posted below.
Doc13.docx
0
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 

Author Comment

by:Rob Fried
ID: 41874015
So orderno 10000 and 10032
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 41874050
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
 

Author Comment

by:Rob Fried
ID: 41874078
Msg 102, Level 15, State 1, Line 13
Incorrect syntax near ')'.

I think it has something to do with the union.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41874172
(no points ...)

Think you need to add an alias for the query the very end:
     
        Select * FROM ( .... etc..... )  tbl
0
 
LVL 28

Assisted Solution

by:Pawan Kumar
Pawan Kumar earned 250 total points
ID: 41874173
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
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41874182
@Rob Fried..

If the question is answered then you have to select the answer and submit.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 41874419
BTW, in cases where one answer builds upon code from another, ie Rgonzo1971's answer, you can always accept multiple answers :-)
0
 
LVL 28

Expert Comment

by:Pawan Kumar
ID: 41874496
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
 
LVL 52

Expert Comment

by:_agx_
ID: 41874607
Thanks. Just to clarify the initial answer was provided by Rgonzo1971 (not me) so any split should be with them  :-)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

808 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