Home » SQL & PL/SQL » SQL & PL/SQL » Min function not working for minimum date (10g Express)
Min function not working for minimum date [message #344310] Fri, 29 August 2008 06:29 Go to next message
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
Re: Min function not working for minimum date [message #344312 is a reply to message #344310] Fri, 29 August 2008 06:33 Go to previous messageGo to next message
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
Re: Min function not working for minimum date [message #344313 is a reply to message #344310] Fri, 29 August 2008 06:34 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ken.nessworthy@apollo-med
Messages: 3
Registered: August 2008
Junior Member
sorted..... just grouped by clientid
Re: Min function not working for minimum date [message #344323 is a reply to message #344310] Fri, 29 August 2008 06:59 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Min function not working for minimum date [message #344348 is a reply to message #344337] Fri, 29 August 2008 08:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Very droll. Cool
Re: Min function not working for minimum date [message #344536 is a reply to message #344348] Sat, 30 August 2008 05:25 Go to previous message
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"




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


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