|
View previous topic :: View next topic
|
| Author |
Message |
veeramanimurugesan
New User
Joined: 03 Nov 2009
Posts: 19
Location: chennai
|
|
|
|
Hi,
Need some help with SELECT clause.
EMP table has a field employee id which is char 7. The first 3 bytes of employee id is branch number and the next 4 is a unique number for that employee.
I have to pick up 1 employee in each branch from this table. This employee id is the key of table.
Table data:
EMP ID
AAA1234
AAA1222
AAA5678
BBB1234
CCC1234
CCC5678
I want my query result to be:
AAA1234
BBB1234
CCC1234
I tried using SELECT DISTINCT(SUBSTR(EMP_ID,1,3)) but this gives me only unique branch number in the output like AAA,BBB,CCC whereas i need the full 7 bytes. I tried to concat this with EMP_ID,4,4 but then it would pick up all employees.
Any employee within a branch could be picked up in random and there is no rule for that.
Please advise. |
|
| Back to top |
|
 |
Pjoe
New User
Joined: 22 Jul 2005
Posts: 17
|
|
|
|
You may use this query..
select max (emp_number) from yourdb.yourtable group by substr(emp_number,1,3); |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
Was this tested before posting? It looks as though it will not produce the wanted results. . .
Veera,
How were the "wanted" results determined? While AAA1234 is first in your "input list" there is no way to guarantee this sequence. . . |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10889
Location: italy
|
|
|
|
IT as a discipline where processes are usually deterministic and repeatable
| Quote: |
| Any employee within a branch could be picked up in random and there is no rule for that. |
the most silly requirement
when You run a query You must set some rules and RANDOM is not one of them
we might argue for ages whether the a MIN or MAX or FIRST/LAST in an unordered select might fit
so it would be better to come up with a better/more sensible requirement
/humor on
and ... just curious, what kind of processing is going to be done on this random employee ?
give him/her an award,
give him/her a raise,
relocate him/her,
tell him/her that the next assignment will be to replace the janitor
terminate him/her,
give him/her an attaboy/attagirl ...
oops I forgot ... for a random employee take a random action 
/humor off
really some teachers in training institutes are really in need of training themselves 
and please do not tell that this is a business requirement, nobody will believe You  |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10889
Location: italy
|
|
|
|
follow on
from Your initial post
| Quote: |
Table data:
EMP ID
AAA1234
AAA1222
AAA5678
BBB1234
CCC1234
CCC5678
I want my query result to be:
AAA1234
BBB1234
CCC1234 |
what if the query returned instead
AAA1222
BBB1234
CCC5678
the randomness requirement would be satisfied, but it will not agree with the sentence
| Quote: |
I want my query result to be:
AAA1234
BBB1234
CCC1234
|
and what if You' d get Your sample the first time and my sample the second time , something else the third time ???
better learn how to ask questions in a better way ! |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
| Quote: |
| IT as a discipline where processes are usually deterministic and repeatable |
If the result is NOT repeatable (i.e. one query, different answers) i suspect some manager or user will not find this acceptable. . .
Even if some kind of "random answer" was somehow acceptable, from what i see here, the "answer" will be the same for a while and then mysteriously change causing considerable confusion. . .  |
|
| Back to top |
|
|
veeramanimurugesan
New User
Joined: 03 Nov 2009
Posts: 19
Location: chennai
|
|
|
|
Thanks experts for the humorous responses!
This ain't a training assignment at first. I just wanted to keep the question generalized and not go into the business part of it to avoid confusions.
Ok this is the table which holds the details of branch managers. So there could be possibilities that 1 branch could have 2 managers or more which is a valid business case. So the output of my current query was fetching 2 branch managers employee id's for some specific branches(AAA1234,AAA5678). But this was causing duplicate rows to be put on some other tables down my job stream.
So i have to pick up only 1 branch manager now. This is just for the case of contacting the branch. So it really does not matter which manager among the managers you contact! To pick up the same manager among the multiple managers we can sequence the employee id on asc or desc etc
Hope my requirement is now clear.. can you please help now? |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10889
Location: italy
|
|
|
|
/HUMOR OFF
| Quote: |
| Thanks experts for the humorous responses! |
horse manure... the responses were not humorous
just the expression of serious concerns with the stupidity (*) of the request and Your poor understanding of general programming logi
and if this is a real business requirement i would even more concerned with the stupidity(*) of the business requirement
(*) whether You like or not
everywhere even the contacts are prioritized according to some <rank> in the organization
even if in the <branch> there are two or more <manglers> one will be somehow have a higher rank
shared command is one of the worst plagues of organizations
nuff said...
| Quote: |
| Hope my requirement is now clear.. can you please help now? |
nope, Your requirement is still clear as mud
given multiple occurrences of something it' s completely up to You, to define an algorithm/criteria for the <right> choice
if You keep on with such silliness the topic will be locked shortly
as already suggested chose min/max according to some column/token that can be ordered/ranked
and if You cannot write a select with a MIN/MAX ....
a suggestion about getting proper/better training stands more than appropriate |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hello,
While there might be multiple managers in a department (0 - n), there should also be a designated "contact" for that department. . .
The query should select the contact for each department rather than some manager . |
|
| Back to top |
|
|
enrico-sorichetti
Superior Member
Joined: 14 Mar 2007
Posts: 10889
Location: italy
|
|
|
|
| Quote: |
| The query should select the contact for each department rather than some manager . |
from the flow of the discussion HR does not care who is the contact
that' s why the TS asked how to get a random occurrences for a query that returns multiple rows  |
|
| Back to top |
|
|
dick scherrer
Moderator Emeritus
Joined: 23 Nov 2006
Posts: 19243
Location: Inside the Matrix
|
|
|
|
Hi Enrico,
Can you imagine the scene when "Fred" is "contacted" one day after "Mary" has been receiving these for a few years. . .
I suppose that if i was given such a "requirement" (any manager will do), i'd look to select the manager with the longest time being a manager in the department. Not sure who i'd contact if the department was between managers. . .
d |
|
| Back to top |
|
|
murugan_mf
Active User
.jpg)
Joined: 31 Jan 2008
Posts: 148
Location: Chennai, India
|
|
|
|
I am not sure whether this is an optimized one..
| Code: |
SELECT SUBSTR(A.EMP_ID,1,3),A.EMP_ID
FROM EMPTABL AS A
GROUP BY SUBSTR(A.EMP_ID,1,3),A.EMP_ID
HAVING SUBSTR(A.EMP_ID,4,4) =
(
SELECT MIN(SUBSTR(EMP_ID,4,4))
FROM EMPTABL WHERE
SUBSTR(EMP_ID,1,3) = SUBSTR(A.EMP_ID,1,3)
); |
|
|
| Back to top |
|
|
chandan.inst
Active User
Joined: 03 Nov 2005
Posts: 275
Location: Mumbai
|
|
|
|
Hi Veera,
Please try below query
| Code: |
SELECT MAX(A.EMP_ID) AS EMPID
FROM EMPTABL A
GROUP BY SUBSTR(A.EMP_ID,1,3) |
Here you can change MAX(A.EMP_ID) to MIN(A.EMP_ID) as per your convenience
I hope this helps.
Regards,
Chandan |
|
| Back to top |
|
|
vasanthz
Global Moderator
Joined: 28 Aug 2007
Posts: 1745
Location: Tirupur, India
|
|
|
|
@chandan.inst
Did you test your code for the above shown input and output? |
|
| Back to top |
|
|
|
|
