SUBSTR Question 2

[acct98]

Is it possible to use the GROUP BY function to come up with one row being selected?
i.e SELECT SUBSTR(DATE,4,6)
FROM X
WHERE DATE LIKE '%-JAN-03
GROUP BY DATE;

 

[carp]

Try:

SELECT SUBSTR(DATE,4,6)
FROM X
WHERE DATE LIKE '%-JAN-03
GROUP BY SUBSTR(DATE,4,6);

 

[Swany]

Please also note that "like '%CONST'" will not use any
index on the column. Assuming the date column
is a DATE datatype, then you might want to do:

SELECT to_char(DATE, 'MON-YY')
  FROM X
 WHERE DATE between '01-JAN-2003' and '31-JAN-2003'
 GROUP BY to_char(DATE, 'MON-YY')
[code]

 .                             
..  Eat, think and be merry .
... ....................... .

 

[CRoberts]

GROUP BY is a clause, not a function. This Clause is used when you are applying an action against a set of records like SUM, COUNT, AVG or some other like function. Said another way, use GROUP BY with any Group function. Do not use GROUP BY with a Single-Row Character function like SUBSTR, makes no sense. Like mixing apples and oranges. Do you really need a GROUP BY? Are trying to get distinct values? If so, consider using DISTINCT, it is much clearer as to the intend of your SELECT. Using SUBSTR and GROUP BY does work syntactically, but what does the result set mean?