getting one instance of multiple records via query

I'm a longtime Access db developer just now really getting into MSDE and
SQL Server 2000.
I have an Access SQL query that retrieves only the first instance of any
date value from a series of records. This gives me a simple list of
available date values fro a dropdown combo box.
My query works great in an Access MDB, but apparently there is no First()
function in SQL Server and I can't find an analog. SQL Server will not
import and reconfigure my queries as it did my tables.
The Access SQL is as follows:
SELECT First(OrderSetupMaster.OrderDate) AS [Order Date Field]
FROM OrderSetupMaster
GROUP BY OrderSetupMaster.OrderDate
ORDER BY First(OrderSetupMaster.OrderDate);
Any suggestions?
Thanks,
Randall ArnoldOn Fri, 14 Oct 2005 15:37:58 -0700, divineblasphemy@.hotmail.com wrote:

>I'm a longtime Access db developer just now really getting into MSDE and
>SQL Server 2000.
>I have an Access SQL query that retrieves only the first instance of any
>date value from a series of records. This gives me a simple list of
>available date values fro a dropdown combo box.
>My query works great in an Access MDB, but apparently there is no First()
>function in SQL Server and I can't find an analog. SQL Server will not
>import and reconfigure my queries as it did my tables.
>The Access SQL is as follows:
>SELECT First(OrderSetupMaster.OrderDate) AS [Order Date Field]
>FROM OrderSetupMaster
>GROUP BY OrderSetupMaster.OrderDate
>ORDER BY First(OrderSetupMaster.OrderDate);
>Any suggestions?
>Thanks,
>Randall Arnold
Hi Randall,
FIRST() is very non-relational in nature. By definition, the rows in a
relational table are UNordered, so there is no first row.
If you want the oldest OrderDate, try MIN(OrderSetupMaster.OrderDate).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Won't that just give me a single row? I need multiple rows, if there are
multiple date values. So if my recordset dates are, say, 12/10/2004
12/11/2004 12/11/2004 12/13/2004, then I expect to pull up 12/10/2004
12/11/2004 12/13/2004... not just a MIN of 12/10/2004 or MAX of
12/13/2004.
Make sense?
Randall Arnold|||Randal,
Based on what criteria (grouping)? Perhaps you are looking for DISTINCT ins
tead?
i.e.,
SELECT DISTINCT(ORDERDATE)AS [Order Date Field]
FROM ORDERSETUPMASTER
ORDER BY ORDERDATE
HTH
Jerry
<randall.arnold@.nokia.com> wrote in message news:u1nOsaR0FHA.3924@.TK2MSFTNGP
14.phx.gbl...
Won't that just give me a single row? I need multiple rows, if there are mu
ltiple date values. So if my recordset dates are, say, 12/10/2004 12/11/200
4 12/11/2004 12/13/2004, then I expect to pull up 12/10/2004 12/11/2004 12/1
3/2004... not just a MIN of 12/10/2004 or MAX of 12/13/2004.
Make sense?
Randall Arnold|||Or...
with the following data in the table:
1 2004-12-10 00:00:00.000
2 2004-12-12 00:00:00.000
2 2004-12-11 00:00:00.000
2 2004-12-12 00:00:00.000
3 2004-12-12 00:00:00.000
3 2004-12-11 00:00:00.000
4 2004-12-11 00:00:00.000
and the query:
SELECT ID, MIN(ORDERDATE)
FROM ORDERSETUPMASTER
GROUP BY ID
ORDER BY MIN(ORDERDATE)
and the resultset:
1 2004-12-10 00:00:00.000
2 2004-12-11 00:00:00.000
3 2004-12-11 00:00:00.000
4 2004-12-11 00:00:00.000
HTH
Jerry
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message news:OZmAUfR0FHA.20
76@.TK2MSFTNGP14.phx.gbl...
Randal,
Based on what criteria (grouping)? Perhaps you are looking for DISTINCT ins
tead?
i.e.,
SELECT DISTINCT(ORDERDATE)AS [Order Date Field]
FROM ORDERSETUPMASTER
ORDER BY ORDERDATE
HTH
Jerry
<randall.arnold@.nokia.com> wrote in message news:u1nOsaR0FHA.3924@.TK2MSFTNGP
14.phx.gbl...
Won't that just give me a single row? I need multiple rows, if there are mu
ltiple date values. So if my recordset dates are, say, 12/10/2004 12/11/200
4 12/11/2004 12/13/2004, then I expect to pull up 12/10/2004 12/11/2004 12/1
3/2004... not just a MIN of 12/10/2004 or MAX of 12/13/2004.
Make sense?
Randall Arnold|||Distinct or Group by will be enough
Regards ,
C#, VB.NET , SQL SERVER , UML , DESIGN Patterns Interview question book
http://www.geocities.com/dotnetinterviews/
My Interview Blog
http://spaces.msn.com/members/dotnetinterviews/|||Yeah, stupid me, DISTINCT did the trick. That occurred to me before I
even saw your post and I was cringing at the prospect of what responses
would look like today...lol.
Thanks,
Randall