Home » SQL & PL/SQL » SQL & PL/SQL » dates and count
dates and count [message #35777] Mon, 15 October 2001 08:51 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.

----------------------------------------------------------------------
Re: dates and count [message #35781 is a reply to message #35777] Mon, 15 October 2001 17:47 Go to previous message
sverch
Messages: 582
Registered: December 2000
Senior Member
Thank you, x, that helped.

----------------------------------------------------------------------
Previous Topic: Re: SOS!!!help, How I can query string in a LONG datatype?
Next Topic: PLS-00103: Found symbol " CREATE ", HELP !!!!!!!!
Goto Forum:
  


Current Time: Thu Mar 28 14:51:41 CDT 2024