[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

SQL Query to Select TOP rows.

Hello,

I want to query a table (myTable) to return the top/first row. Following is a sample data from mytable:

ID           EmpNum             Date                                  Comments
7            123        2011-02-14 00:00:00.000      Approved
6            123        2011-02-16 00:00:00.000      Approved      
5            123        2011-02-15 00:00:00.000      Approved by Manager.      
4            123        2011-02-14 00:00:00.000      Approved by Manager.
3            123        2011-02-15 00:00:00.000      Approved      
2            456        2011-02-16 00:00:00.000      Approved by Manager.      
1            456        2011-02-18 00:00:00.000      Approved by Manager.

When I run the query, I want to return all the rows for an Emp#. Also, I only want to return top row if there are any multiple values for the same date. Meaning; when I query on Emp# 123, I want to get:

ID          EmpNum             Date                                  Comments
7            123        2011-02-14 00:00:00.000      Approved
6            123        2011-02-16 00:00:00.000      Approved      
5            123        2011-02-15 00:00:00.000      Approved by Manager

Thus, returning the distinct and the TOP most rows if similar dates exist. I currently have the code (see attached) to return the distinct values but it doesn't get me the latest value which was entered in the table. Here's what my query fetches currently:

ID           EmpNum             Date                                  Comments
6            123        2011-02-16 00:00:00.000      Approved      
4            123        2011-02-14 00:00:00.000      Approved by Manager.
3            123        2011-02-15 00:00:00.000      Approved      


I hope my explanation isn't too complicated and confusing. Thanks for any help you can provide.
select distinct date, empNum, ID, Comments from myTable where empNum = '123' and datepart(yyyy, date) = '2011' order by date

Open in new window

0
SasDev
Asked:
SasDev
2 Solutions
 
knightEknightCommented:
Try this:

order by ID desc, date
0
 
knightEknightCommented:
select distinct T.date, T.empNum, T.ID, T.Comments
from myTable T
join ( select [Date], max(ID) as ID from myTable group by [Date] ) D
  on T.[Date] = D.[Date]
 and T.ID = D.ID
where empNum = '123'
  and datepart(yyyy, date) = '2011'
order by date
0
 
Imran Javed ZiaCommented:
Hi,
You can use following Query

select distinct date, empNum, ID, Comments from myTable

where ID in (
      select max(ID) as ID from myTable
      where empNum = '123'
      and datepart(yyyy, date) = '2011'
      Group by date, empNum
)

order by date

Thanks
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
kamindaCommented:
How do you define the top one as
2011-01-15 00:00:00.000 same for all the rules. How do you define which column you need. What is the criteria?
0
 
knightEknightCommented:
Also, in my previous post, the join statement will be more efficient by adding the EmpNum:

join ( select [Date], max(ID) as ID from myTable where EmpNum = '123' group by [Date] ) D
0
 
SasDevAuthor Commented:
The comment by IJZ was the best solution for my query.

I do appreciate comments by knightEknight though. Thanks so much for the solutions you guys provided.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now