| Maximum Date [message #600844] |
Wed, 13 November 2013 01:46  |
 |
sahnra
Messages: 39
Registered: November 2013
Location: India
|
Member |
|
|
what will be query to take out maximum chECK_DATE of employees from employee table but the result do not show previous check date, only it will show current check date For example for employee - 104,253 -10/25/2013----->current_date
company - employee - checkDate
1 - 104,253 - 10/25/2013
1 - 104,240- 10/25/2013
1 - 104,253- 10/11/2013
1 - 104,240- 10/11/2013
[Updated on: Wed, 13 November 2013 01:47] Report message to a moderator |
|
|
|
|
|
| Re: Maximum Date [message #600849 is a reply to message #600844] |
Wed, 13 November 2013 02:50  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
sahnra wrote on Wed, 13 November 2013 13:16For example for employee - 104,253 -10/25/2013----->current_date
You have employee as 104,253. So, 104 and 253 are two different employees, right? Or, are you storing it together in your employee table?
|
|
|
|
|
|
| Re: Maximum Date [message #600854 is a reply to message #600851] |
Wed, 13 November 2013 04:30 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
sahnra wrote on Wed, 13 November 2013 08:57104253 is a single employee ID
Have you read the documentation that I linked to? Have you resolved your 'issue'?
|
|
|
|
|
|
| Re: Maximum Date [message #600859 is a reply to message #600858] |
Wed, 13 November 2013 05:14 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
1. Post your query
2. Quote your post where you said that you wanted to delete data.
3. Show us that you have looked up the documentation for the delete statement.
4. Show us that you have tried to apply your newly gained knowledge of the delete statement to your issue (it's fine if you have failed, just show us that you have put some effort in).
|
|
|
|
|
|
|
|
| Re: Maximum Date [message #600863 is a reply to message #600862] |
Wed, 13 November 2013 05:42 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
shouldn't the list of columns in the select bit match the list of columns in the group by?
Show us the delete you used.
|
|
|
|
|
|
| Re: Maximum Date [message #600865 is a reply to message #600862] |
Wed, 13 November 2013 05:43 |
pablolee
Messages: 2882
Registered: May 2007
Location: Scotland
|
Senior Member |
|
|
You are including columns in your query that you have not mentioned in your original op with sample data. It's time to start over.
Provide a working test case (create table and insert statements)
Provide expected output and an explanation of that output.
Use code tags whenever you post code (it makes it much easier to read).
You seem to be mixing up terms too. ONLY use the term delete if you want to permanently REMOVE rows from a table. (Based on what you have posted, you don't seem to want to do that)
|
|
|
|
| Re: Maximum Date [message #600866 is a reply to message #600862] |
Wed, 13 November 2013 05:45 |
 |
dariyoosh
Messages: 538
Registered: March 2009
Location: France
|
Senior Member |
|
|
In the OP you just talked about a single table "EMPLOYEES", you never talked about a join of 4 tables, so obviously the output could be different depending on the semantic of your GROUP by clause and the structure of the join.
[Edit]: Sorry I didn't see pablolee's remark, before posting 
[Updated on: Wed, 13 November 2013 05:47] Report message to a moderator |
|
|
|
| Re: Maximum Date [message #600869 is a reply to message #600866] |
Wed, 13 November 2013 06:18 |
|
sahnra
Messages: 39
Registered: November 2013
Location: India
|
Member |
|
|
Sorry for that can you please let me know one thing how i can remove duplicate of employee from this query
SELECT A.COMPANY, a. EMPLOYEE, A.Process_level, MAX(B.CHECK_DATE) Last_check_date
FROM LAWSON.EMPLOYEE A, LAWSON.PAYMASTR B,
LAWSON.COMPROF C, LAWSON.TAXGROUP D WHERE A.EMPLOYEE = B.EMPLOYEE
AND A.PROCESS_LEVEL = C.PROCESS_LEVEL
AND A.COMPANY = B.COMPANY
AND A.COMPANY = C.COMPANY
And A.company = D.company
and A.PROCESS_LEVEL= D.process_level
AND C.COMPANY = '1'
Group by A.COMPANY,A.Process_level, A.EMPLOYEE
ORDER BY B.CHECK_DATE DESC
[Updated on: Wed, 13 November 2013 06:19] Report message to a moderator |
|
|
|
|
|
| Re: Maximum Date [message #600873 is a reply to message #600864] |
Wed, 13 November 2013 06:56 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
I don't appreciate having to ask this multiple times:
cookiemonster wrote on Wed, 13 November 2013 11:42And can you please read and follow How to use [code] tags and make your code easier to read?
Do so before your next post please.
Past that, the only why that query is giving dupes is if the EMPLOYEE table contains multiple records with the same EMPLOYEE and different COMPANY or Process_level.
So you need to show us (as already asked) an example of your current data and an example of the expected output.
|
|
|
|
| Re: Maximum Date [message #600874 is a reply to message #600869] |
Wed, 13 November 2013 07:33 |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
sahnra wrote on Wed, 13 November 2013 13:18Sorry for that can you please let me know one thing how i can remove duplicate of employee from this query
Just remove it (column EMPLOYEE) from SELECT and GROUP BY clauses. If case of need, introduce any aggregate function on that column fulfilling the exact requirements. In fact, it is impossible to deduce it from your posts. More precisely, it is impossible to deduce anything from your posts at all as they are continually changing.
If you want more precise answer/solution, just re-read pablolee's post (http://www.orafaq.com/forum/mv/msg/190313/600865/#msg_600865) and start collecting the demanding info. I believe that you may find the solution yourself when you try fulfilling it.
|
|
|
|
| Re: Maximum Date [message #600877 is a reply to message #600869] |
Wed, 13 November 2013 08:19 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
sahnra wrote on Wed, 13 November 2013 17:48can you please let me know one thing how i can remove duplicate of employee from this query
This link might help you in better understanding on how to remove duplicates.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
