dates and count [message #35777] |
Mon, 15 October 2001 08:51 |
sverch
Messages: 582 Registered: December 2000
|
Senior Member |
|
|
I have a table where one key may have several dates:
KEY_COLUMN DATE_COLUMN
abc 'O1-JAN-1994'
abc '12-FEB-1996'
abc '06-sep-1999'
dfg '06-sep-1994'
dfg '12-dec-1998'
etc.
In my quite compliceted select with a subquery I am trying to get count for records with greatest date_column.
Right now my query is something like this, but I am not sure the result will be accurate:
SELECT count(a.key_column) FROM my_table a, my_table b
WHERE a.key_column=b.key_column
AND a.date_column>b.date_column
AND a.date_column<SYSDATE
AND key_column IN (
(SELECT ...FROM ...
WHERE ...);
I am also trying to play with GREATEST, but maybe you have better ideas that I will appreciate.
----------------------------------------------------------------------
|
|
|
correction [message #35778 is a reply to message #35777] |
Mon, 15 October 2001 08:54 |
sverch
Messages: 582 Registered: December 2000
|
Senior Member |
|
|
Sorry,the query looks like this:
SELECT count(a.key_column) FROM my_table a, my_table b
WHERE a.key_column=b.key_column
AND a.date_column>b.date_column
AND a.key_column IN (
(SELECT ...FROM ...
WHERE ...);
----------------------------------------------------------------------
|
|
|
Re: dates and count [message #35779 is a reply to message #35777] |
Mon, 15 October 2001 10:23 |
Jon
Messages: 483 Registered: May 2001
|
Senior Member |
|
|
It would be helpful if you would specify what the exact results you would expect are. If you are expecting
VAL1 COUNT(DATE1)
-------------------- ------------
abc 3
then a query would be...
select val1, count(date1) from datetest
where val1 IN (select val1 from datetest where date1 = (select max(date1) from datetest))
group by val1;
If you ACTUALLY want a count of records with the greatest date column (as you stated), then the query is simpler...
14:17:45 ==> select count('x') from datetest where date1 = (select max(date1) from datetest);
COUNT('X')
----------
1
My guess is you wanted the former.
----------------------------------------------------------------------
|
|
|
|