min(date) + 1

I know you can use the min function to get the first date within a
recordset. However, is there a way to get the second date in a
recordset? In other words, is there a way to get "min(date) + 1" or a
"next-to-earliest date" essentially?Try somehthing like...

select min(a.date) from (select date from table b where b.date > (select
min(c.date) from table c)) a

This should get the next earliest date not necessarily the next min date if
there are duplicate min dates.

<imani_technology_spam@.yahoo.com> wrote in message
news:1127338921.903049.218810@.z14g2000cwz.googlegr oups.com...
>I know you can use the min function to get the first date within a
> recordset. However, is there a way to get the second date in a
> recordset? In other words, is there a way to get "min(date) + 1" or a
> "next-to-earliest date" essentially?|||select top 1 a.date from
(select top 2 date from table order by date ) a
order by a.date desc|||On Wed, 21 Sep 2005 23:21:52 GMT, Danny wrote:

>Try somehthing like...
>select min(a.date) from (select date from table b where b.date > (select
>min(c.date) from table c)) a
>This should get the next earliest date not necessarily the next min date if
>there are duplicate min dates.

Hi Danny,

No need to use a derived table, though. This'll work as well:

SELECT MIN(a.date)
FROM table AS a
WHERE a.date > (SELECT MIN(b.date) FROM b.table))

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Min not working

Hi all:
Because me not this functioning the following one query.

simply me not the function respects min, because?, that I am doing badly?

Select Distinct(Min(c.gentime)),
a.CardNumber,a.CardHolderId,a.Deleted,
b.RecordId,b.FirstName,b.LastName,b.Deleted,b.Note 4,
c.param3,c.param2,c.param1,c.recvtime,c.gentime,c. link1,c.link2,c.link3,c.deleted,c.recordid,c.seqid
From Card a,
CardHolder b,
History c
Where ((a.cardholderid = b.recordid)And(b.recordid=c.link3))
And(a.Deleted = 0)And (b.Deleted = 0)And(c.deleted = 0)
And(a.cardnumber Between 1500 And 1600)
And(b.note4 <> 'Mantenimiento')
And(c.RecvTime >= CONVERT(DATETIME, '2006-10-01 00:00:00', 102))And(c.RecvTime <= CONVERT(DATETIME, '2006-10-12 23:59:59', 102))
And(c.Link1=10)
Group By a.CardNumber, a.CardHolderID, a.Deleted,
b.RecordID, b.Deleted, b.FirstName, b.LastName, b.Note4,
c.Deleted, c.RecvTime, c.GenTime, c.Param1, c.Param2, c.Param3, c.Link1, c.Link2, c.Link3, c.Link4,c.recordid,c.seqid
Order By a.CardNumber,c.recvtime

Thanks.:confused:the problem is because you have c.Link4 in the GROUP BY but not in the SELECT

also, please note: DISTINCT is not a function

try this --select Min(c.gentime) as min_gentime
, a.CardNumber
, a.CardHolderId
, a.Deleted
, b.RecordId
, b.FirstName
, b.LastName
, b.Deleted
, b.Note4
, c.param3
, c.param2
, c.param1
, c.recvtime
, c.gentime
, c.link1
, c.link2
, c.link3
, c.deleted
, c.recordid
, c.seqid
from Card a
inner
join CardHolder b
on b.recordid = a.cardholderid
inner
join History c
on c.link3 = b.recordid
where a.Deleted = 0
and b.Deleted = 0
and c.deleted = 0
and a.cardnumber between 1500 and 1600
and b.note4 <> 'Mantenimiento'
and c.RecvTime >= '2006-10-01'
and c.RecvTime < '2006-10-13'
and c.Link1 = 10
group
by a.CardNumber
, a.CardHolderId
, a.Deleted
, b.RecordId
, b.FirstName
, b.LastName
, b.Deleted
, b.Note4
, c.param3
, c.param2
, c.param1
, c.recvtime
, c.gentime
, c.link1
, c.link2
, c.link3
, c.deleted
, c.recordid
, c.seqid
order
by a.CardNumber
, c.recvtime|||thanks by answering, the problem is, that the inner join does not respect me the rank dated, I need a value by each employee, by each I gave of the rank.

Thanks.:confused:|||okay, try this --select c.gentime as min_gentime
, a.CardNumber
, a.CardHolderId
, a.Deleted
, b.RecordId
, b.FirstName
, b.LastName
, b.Deleted
, b.Note4
, c.param3
, c.param2
, c.param1
, c.recvtime
, c.gentime
, c.link1
, c.link2
, c.link3
, c.deleted
, c.recordid
, c.seqid
from Card a
inner
join CardHolder b
on b.recordid = a.cardholderid
inner
join History c
on c.link3 = b.recordid
where a.Deleted = 0
and b.Deleted = 0
and c.deleted = 0
and a.cardnumber between 1500 and 1600
and b.note4 <> 'Mantenimiento'
and c.RecvTime >= '2006-10-01'
and c.RecvTime < '2006-10-13'
and c.Link1 = 10
and c.gentime =
( select min(c.gentime)
from History
where link3 = b.recordid
and deleted = 0
and RecvTime >= '2006-10-01'
and RecvTime < '2006-10-13'
and Link1 = 10 )
order
by a.CardNumber
, c.recvtime|||thanks but the following error marks me:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

:eek:|||i'm going to move this query to the SQL Server forum

i don't understand the error message, maybe someone else does|||Not completely sure what is going on here, but trying moving the subquery to the JOIN clause:select c.gentime as min_gentime
, a.CardNumber
, a.CardHolderId
, a.Deleted
, b.RecordId
, b.FirstName
, b.LastName
, b.Deleted
, b.Note4
, c.param3
, c.param2
, c.param1
, c.recvtime
, c.gentime
, c.link1
, c.link2
, c.link3
, c.deleted
, c.recordid
, c.seqid
from Card a
inner
join CardHolder b
on b.recordid = a.cardholderid
inner
join History c
on c.link3 = b.recordid
inner
join --subquery
(select link3,
min(c.gentime) as gentime
from History
where link3 = b.recordid
and deleted = 0
and RecvTime >= '2006-10-01'
and RecvTime < '2006-10-13'
and Link1 = 10
group by link3) subquery
on subquery.link3 = b.recordid
and subquery.gentime = c.gentime
where a.Deleted = 0
and b.Deleted = 0
and c.deleted = 0
and a.cardnumber between 1500 and 1600
and b.note4 <> 'Mantenimiento'
and c.RecvTime >= '2006-10-01'
and c.RecvTime < '2006-10-13'
and c.Link1 = 10
order
by a.CardNumber
, c.recvtime

Min Function Problem

<%
sql="select MIN(psprice)as askBA from transactions where namecompany='BA'and act='Limit sell order'"
set AskBA=server.createobject("adodb.recordset")
AskBA.open sql,conn,1,3%>
<td height="22" width="41"><div align="center">
<% if not AskBA.eof then
response.write AskBA("askBA")
else
response.write "-"
end if
AskBA.close%>

When there is Data which namecompany=BA and act=Limit sell order in table transactions , it's OK. It shows the minimun price.

But the problem is that when there is no Data which namecompany=BA and act=Limit sell order in table transactions, it should show - But it does not show anything.
I don't know why it does not go to condition Else.The query has returned a row, with NULL for askBA. So EOF is not true, instead you need to test the value of askBA.