MIN clause inSQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

MIN clause inSQL

Author

Topic

korssane
Posting Yak Master

104 Posts

Posted - 2010-04-05 : 12:11:44

i am stuck with the following simple syntax..
i need to pull the oldest billnumber ..ultimately i need to see the ticket number with the oldest date .

here is my select statement using an open query to oracle..

(SELECT * FROM OPENQUERY(B_COUNT,
'SELECT
TICKETID,
MIN(CR_DATE)
FROM TABLE
WHERE
(
CR_DATE >= ''1262304001'' AND CREATE_DATE < (trunc(SYSDATE) - to_date(''01-JAN-1970'',''DD-MON-YYYY'')) * (86400) AND
STATUS = ''0'' AND
)

'

i am having ORA-00937: not a single-group group function

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 12:14:37

try:-



(SELECT * FROM OPENQUERY(B_COUNT, 
'
SELECT t.*
FROM TABLE t
JOIN(
SELECT
TICKETID,
MIN(CR_DATE) AS MInDate
FROM TABLE
GROUP BY TICKETID)t1
ON t1.MInDate = t.CR_DATE
AND t1.TICKETID = t.TICKETID
WHERE
(
t.CR_DATE >= ''1262304001'' AND t.CREATE_DATE < (trunc(SYSDATE) - to_date(''01-JAN-1970'',''DD-MON-YYYY'')) * (86400) AND
t.STATUS = ''0'' AND
)

'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

korssane
Posting Yak Master

104 Posts

Posted - 2010-04-05 : 12:43:44

Hi thanks,
but is there a way to remove the JOIn cuz i can not use it within the Openquery statment

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 12:56:06

nope. if you want minimum for each group

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 12:57:42

or try this


(SELECT * FROM OPENQUERY(B_COUNT, 
'
SELECT t.*
FROM TABLE t
WHERE
(
t.CR_DATE >= ''1262304001'' AND t.CREATE_DATE < (trunc(SYSDATE) - to_date(''01-JAN-1970'',''DD-MON-YYYY'')) * (86400) AND
t.STATUS = ''0'' AND
)
AND CR_DATE IN (SELECT MIN(CR_DATE) FROM Table WHERE TICKETID =t. TICKETID )
'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

korssane
Posting Yak Master

104 Posts

Posted - 2010-04-05 : 12:58:39

hi,

i do not want to group anything.
I just want to have the oldest date with its corresponding ticket id .

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 13:03:46

quote:
Originally posted by korssane

hi,

i do not want to group anything.
I just want to have the oldest date with its corresponding ticket id .

Thanks

that i understood. but for that you need to find out min(date) after grouping by ticketid or use subquery suggestion

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

korssane
Posting Yak Master

104 Posts

Posted - 2010-04-05 : 13:06:34

for the 2nd query it gives me this error :
returned message "Data type is not supported.".

korssane
Posting Yak Master

104 Posts

Posted - 2010-04-05 : 13:07:16

the problem is i am using the openquery statement it is too limited..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-05 : 13:09:53

I think it would be best if you can post this in oracle forums as you're trying to run this on oracle server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

korssane
Posting Yak Master

104 Posts

Posted - 2010-04-05 : 13:13:19

ok thanks Visakh16 for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-06 : 00:00:21

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources