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.
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 dateSOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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?
2011-01-15 00:00:00.000 same for all the rules. How do you define which column you need. What is the criteria?
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
join ( select [Date], max(ID) as ID from myTable where EmpNum = '123' group by [Date] ) D
ASKER
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.
I do appreciate comments by knightEknight though. Thanks so much for the solutions you guys provided.
order by ID desc, date