need sql help for select min

[sb_akarmarkar]

But you are converting date to char and then applying the MIN to char value
I think it should be.. TO_CHAR(MIN(PERIOD.PERSTR_0), 'YYYY-MM-DD HH24:MI:SS')) ...

It didn't work for me when I tried using what you have mentioned and hence I stuck to what I mentioned and it was working fine for me i.e. giving the desired results.

Why dont you try sql in database and see result .... i am sure you will get more clarification...


Thanks,
Anupam

[poonamgoyal]

well i have some simple query using subqueries if you want to try:

SELECT PERIOD.CPY_0,PERIOD.FIYNUM_0,PERIOD.PERNUM_0, TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS'),
FROM #LNomDos#.PERIOD
WHERE PERIOD.PERSTR_0 IN (SELECT MIN(TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS')) FROM #LNomDos#.PERIOD)


IT works for my table don't know about your specifications whether to use subqueries or not as i m a fresher to datastage... u just see to it if it helps

[sb_akarmarkar]

well i have some simple query using subqueries if you want to try:

SELECT PERIOD.CPY_0,PERIOD.FIYNUM_0,PERIOD.PERNUM_0, TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS'),
FROM #LNomDos#.PERIOD
WHERE PERIOD.PERSTR_0 IN (SELECT MIN(TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS')) FROM #LNomDos#.PERIOD)


IT works for my table don't know about your specifications whether to use subqueries or not as i m a fresher to datastage... u just see to it if it helps

It is fine .... use it in user-defined query
small modification need to do it for query...

SELECT PERIOD.CPY_0,PERIOD.FIYNUM_0,PERIOD.PERNUM_0, TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS'),
FROM #LNomDos#.PERIOD
WHERE PERIOD.PERSTR_0 IN (SELECT MIN(PERIOD.PERSTR_0) FROM #LNomDos#.PERIOD)



Thanks,
Anupam

[ray.wurlod]

Since MIN can only be a single value, your WHERE clause can be made more efficient (depending on whether the query optimizer does this anyway). Relace "IN" with "=" as the comparison operation.

Code: Select all

WHERE PERIOD.PERSTR_0 = (SELECT MIN(PERIOD.PERSTR_0) FROM #LNomDos#.PERIOD) 

[poonamgoyal]

well i have some simple query using subqueries if you want to try:

SELECT PERIOD.CPY_0,PERIOD.FIYNUM_0,PERIOD.PERNUM_0, TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS'),
FROM #LNomDos#.PERIOD
WHERE PERIOD.PERSTR_0 IN (SELECT MIN(TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS')) FROM #LNomDos#.PERIOD)


IT works for my table don't know about your specifications whether to use subqueries or not as i m a fresher to datastage... u just see to it if it helps

It is fine .... use it in user-defined query
small modification need to do it for query...

SELECT PERIOD.CPY_0,PERIOD.FIYNUM_0,PERIOD.PERNUM_0, TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS'),
FROM #LNomDos#.PERIOD
WHERE PERIOD.PERSTR_0 IN (SELECT MIN(PERIOD.PERSTR_0) FROM #LNomDos#.PERIOD)



Thanks,
Anupam

ya i knew it but just ignored amending it for developer to take care as where he want to format

[poonamgoyal]

Since MIN can only be a single value, your WHERE clause can be made more efficient (depending on whether the query optimizer does this anyway). Relace "IN" with "=" as the comparison operation.
[cod ...

I never knew about this use of '=' thanks, i tried it works fine, if it improves performance then i got a good performance tuning tip from you

[sb_akarmarkar]

SELECT A.CPY_0, A.FIYNUM_0, A.PERNUM_0, TO_CHAR(A.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS'),
FROM #LNomDos#.PERIOD A
WHERE A.PERSTR_0 = (SELECT MIN(B.PERSTR_0) FROM #LNomDos#.PERIOD B WHERE B.CPY_0 = A.CPY_0)

One more .. Previously totaly overlook..

Thanks,
Anupam

[poonamgoyal]

what is the need of creating these aliases as it ll add to performance overhead i think

[sb_akarmarkar]

what is the need of creating these aliases as it ll add to performance overhead i think

If you look at query you will understand need of aliases and it wont add any overhead ,just increases your readability ...

Thanks,
Anupam