need sql help for select min
Moderators: chulett, rschirm, roy
need sql help for select min
Hi all ,
I'm trying to select the minimun date of table, for example :
-----------------------------------
num1 01/01/06 --> ok
num1 01/02/06
num1 01/11/07
num2 01/01/06 --> ok
num2 01/03/06
-----------------------------------
result expect :
num1 01/01/06
num2 01/01/06
I use min() but when I perform a view data I have no difference.
Thanks in advance !
I'm trying to select the minimun date of table, for example :
-----------------------------------
num1 01/01/06 --> ok
num1 01/02/06
num1 01/11/07
num2 01/01/06 --> ok
num2 01/03/06
-----------------------------------
result expect :
num1 01/01/06
num2 01/01/06
I use min() but when I perform a view data I have no difference.
Thanks in advance !
-
- Premium Member
- Posts: 385
- Joined: Tue Oct 07, 2003 4:55 am
what is your sql? Need to take a look at that.
Also, when you write your own sql, press OK, get out of the stage, go back in and check wheather the changes you made were saved or not. I know the DB2 API gave me a lot of trouble once with this.
Also, when you write your own sql, press OK, get out of the stage, go back in and check wheather the changes you made were saved or not. I know the DB2 API gave me a lot of trouble once with this.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
This is my sql code :
-----------------------------------------------------
SELECT PERIOD.CPY_0,PERIOD.FIYNUM_0,PERIOD.PERNUM_0,MIN(TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS')),
FROM #LNomDos#.PERIOD
GROUP BY PERIOD.CPY_0
----------------------------------------------------
I want to keep just lines with minimum date ...
-----------------------------------------------------
SELECT PERIOD.CPY_0,PERIOD.FIYNUM_0,PERIOD.PERNUM_0,MIN(TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS')),
FROM #LNomDos#.PERIOD
GROUP BY PERIOD.CPY_0
----------------------------------------------------
I want to keep just lines with minimum date ...
Your SQL doesn't work. You either need to add more columns to the GROUP clause or use a subselect. You also need to do the MIN on the data before converting it to char. You also have a trailing comma in the column list that won't let the SQL work.
Either:
Or:
Both give different answers, but you need to decide which one is appropriate.
Either:
Code: Select all
SELECT PERIOD.CPY_0,PERIOD.FIYNUM_0,PERIOD.PERNUM_0, TO_CHAR(MIN(PERIOD.PERSTR_0), 'YYYY-MM-DD HH24:MI:SS')
FROM #LNomDos#.PERIOD
GROUP BY PERIOD.CPY_0,PERIOD.FIYNUM_0,PERIOD.PERNUM_0
Code: Select all
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.CPY_0||PERIOD.PERSTR_0 IN
(SELECT PERIOD.CPY_0||MIN(PERIOD.PERSTR_0)
FROM #LNomDos#.PERIOD GROUP BY PERIOD.CPY_0
)
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Try this
or you should also perform some kind of aggregate functions on columns PERIOD.FIYNUM_0,PERIOD.PERNUM_0
Code: Select all
SELECT PERIOD.CPY_0,PERIOD.FIYNUM_0,PERIOD.PERNUM_0, MIN(TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS')),
FROM #LNomDos#.PERIOD
GROUP BY PERIOD.CPY_0, PERIOD.FIYNUM_0, PERIOD.PERNUM_0
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
kris007 wrote:Try thisor you should also perform some kind of aggregate functions on columns PERIOD.FIYNUM_0,PERIOD.PERNUM_0Code: Select all
SELECT PERIOD.CPY_0,PERIOD.FIYNUM_0,PERIOD.PERNUM_0, MIN(TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS')), FROM #LNomDos#.PERIOD GROUP BY PERIOD.CPY_0, PERIOD.FIYNUM_0, PERIOD.PERNUM_0
MIN(TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS'))
Won't look correct .......... Will it give desire result.........
Thanks,
Anupam
Will work as good as it cansb_akarmarkar wrote: MIN(TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS'))
Won't look correct .......... Will it give desire result.........
Yes, it gives desired results. I used the MAX function many a times. So, shouldn't be any different for MIN function.
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
-
- Participant
- Posts: 232
- Joined: Fri Sep 30, 2005 4:52 am
- Contact:
But you are converting date to char and then applying the MIN to char valuekris007 wrote:Will work as good as it cansb_akarmarkar wrote: MIN(TO_CHAR(PERIOD.PERSTR_0, 'YYYY-MM-DD HH24:MI:SS'))
Won't look correct .......... Will it give desire result.........
Yes, it gives desired results. I used the MAX function many a times. So, shouldn't be any different for MIN function.
I think it should be.. TO_CHAR(MIN(PERIOD.PERSTR_0), 'YYYY-MM-DD HH24:MI:SS')) ...
Thanks,
Anupam
You have not posted an error message. First we fix your SQL, but you switched to using a different method while we are wasting time on your SQL.
Since you have not posted the error message and we can't read your mind, I'm guessing you're getting a data type error. If you use the Aggregator stage with DATE or TIMESTAMP data type, just put the data into YYYY-MM-DD format and toggle the data types to VARCHAR as the Aggregator wants DATE in the internal format, which is stupid. If you change the metadata to VARCHAR for use in the Aggregator it won't complain.
Do us a favor and respond to our points. It's really difficult to help someone who isn't paying attention.
Since you have not posted the error message and we can't read your mind, I'm guessing you're getting a data type error. If you use the Aggregator stage with DATE or TIMESTAMP data type, just put the data into YYYY-MM-DD format and toggle the data types to VARCHAR as the Aggregator wants DATE in the internal format, which is stupid. If you change the metadata to VARCHAR for use in the Aggregator it won't complain.
Do us a favor and respond to our points. It's really difficult to help someone who isn't paying attention.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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.sb_akarmarkar wrote:
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')) ...
Kris
Where's the "Any" key?-Homer Simpson
Where's the "Any" key?-Homer Simpson
HI kjaouhari,kjaouhari wrote:Ok I'm agree with you that I'm not very explicit and I didn't mentionned the error message .
But It's not stupid to switch to using an other method, It can be interesting for Dsxians to know the different solution of a request ...
It is no wehere mentioned as stupidity to switch between options. The all required is the exact requirement of yours, and the exact process that currently follow and the exact error mesage if at all you get.
So that it would be easy for the posters to judge and answer for your query.
Else you force them to make some wild assumptions, which will again wont help you.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
I think you didn't read my recent response carefully.
As for this thread, your attention wandered, you posted one issue, didn't use our responses, then came up with new issues for a different set of problems and posted that. Since your new problems relate to trying something different, you made our answers irrelevant and uselss, thus wasting our time.
I was commenting on the Aggregator stage requirement to process DATE values in the internal DS BASIC format.as the Aggregator wants DATE in the internal format, which is stupid.
As for this thread, your attention wandered, you posted one issue, didn't use our responses, then came up with new issues for a different set of problems and posted that. Since your new problems relate to trying something different, you made our answers irrelevant and uselss, thus wasting our time.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle