OraFAQ Forum: SQL & PL/SQL » Min function not working for minimum date

Home » SQL & PL/SQL » SQL & PL/SQL » Min function not working for minimum date (10g Express)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Min function not working for minimum date [message #344310]

Fri, 29 August 2008 06:29

ken.nessworthy@apollo-med
Messages: 3
Registered: August 2008

Junior Member

Hi All,

I have a simple query that should return the first date but instead is returning all the dates in a table...

Can someone tell me where I am going wrong

Query
SELECT qs.clientid, min(qs.responded)
FROM querystatus qs, activequery aq
WHERE qs.queryid = aq.queryid
AND aq.guid = '{92AC13C5-CF44-478E-AD46-737C1077922D}'
AND qs.clientid = 909
GROUP BY qs.responded, qs.clientid
ORDER BY 2

Returned
CLIENTID MIN(QS.RESPONDED)
----------- --------------------
909 08/02/2008 05:39:36
909 30/03/2008 19:54:28
909 19/04/2008 11:39:45
909 20/04/2008 12:50:55
909 20/04/2008 18:03:59
909 08/06/2008 23:39:45
909 15/06/2008 19:18:12
909 19/06/2008 07:01:45
909 22/06/2008 21:02:22

I only require the first record.

Kind regards

Ken

Report message to a moderator

Re: Min function not working for minimum date [message #344312 is a reply to message #344310]

Fri, 29 August 2008 06:33

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

If you group by A then obviously each group has only one A and it is the minimum (as well as the maximum and so on).

MIN is working, only you are not.

Regards
Michel

Report message to a moderator

Re: Min function not working for minimum date [message #344313 is a reply to message #344310]

Fri, 29 August 2008 06:34

dwarak.k
Messages: 61
Registered: June 2008
Location: Hyderabad

Member

Remove qs.responded from the group by. If you want the minimum date for each qs.clientid

[Updated on: Fri, 29 August 2008 06:35]

Report message to a moderator

Re: Min function not working for minimum date [message #344319 is a reply to message #344310]

Fri, 29 August 2008 06:44

ken.nessworthy@apollo-med
Messages: 3
Registered: August 2008

Junior Member

sorted..... just grouped by clientid

Report message to a moderator

Re: Min function not working for minimum date [message #344323 is a reply to message #344310]

Fri, 29 August 2008 06:59

ken.nessworthy@apollo-med
Messages: 3
Registered: August 2008

Junior Member

Many thanks for the help, works great now but busy trying to use it in a more complicated query..

/K

[Updated on: Fri, 29 August 2008 07:00]

Report message to a moderator

Re: Min function not working for minimum date [message #344337 is a reply to message #344319]

Fri, 29 August 2008 07:44

joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA

Senior Member

ken.nessworthy@apollo-med wrote on Fri, 29 August 2008 07:44

sorted..... just grouped by clientid

GROUP BY does not translate to sorted.

Report message to a moderator

Re: Min function not working for minimum date [message #344348 is a reply to message #344337]

Fri, 29 August 2008 08:32

JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...

Senior Member

Very droll. Cool

Cool

Report message to a moderator

Re: Min function not working for minimum date [message #344536 is a reply to message #344348]

Sat, 30 August 2008 05:25

dr.s.raghunathan
Messages: 540
Registered: February 2008

Senior Member

hi,

i thought droll is some technical word and want to know the meaning. Now thanks to know a new word droll

Quote:

droll
adj : comical in an odd or whimsical manner; "a droll little man with a quiet tongue-in-cheek kind of humor"

Report message to a moderator

Previous Topic:	Need suggestion
Next Topic:	SQL and PL\SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 14:45:44 CST 2024