[AccessD] Top1

Hi Gustav,

Yup, he states it in his response as well that LAST is a JET
translation to the subquery solution from Sql Server.

--
Francisco

On 1/26/07, Gustav Brock <Gustav at cactus.dk> wrote:
> Hi Francisco
>
> Oh, I didn't know that? So much for T-SQL. Or is First and Last a JET SQL speciality?
>
> However, I located this solution by Michel Walsh:
>
> http://groups.google.com/group/microsoft.public.access.adp.sqlserver/browse_thread/thread/dc61e99eb5f8d156/c7f8241b692915c3%23c7f8241b692915c3
>
> /gustav
>
> >>> fhtapia at gmail.com 26-01-2007 19:18:02 >>>
> John is working in a Sql Server query, not Access for this query...
> iirc First is not a valid keyword in Tsql,
>
> You could however use the Min keyword for the following solution
>
> SELECT MIN(PKID), Address1, Zip5, Zip4
> FROM #TEST
> GROUP BY address1, zip5, Zip4
>
>
> /*
> Below is the sample data I created to make it happen
> */
> CREATE TABLE #TEST (PKID INT , Address1 VARCHAR(50), Zip5 VARCHAR(5),
> Zip4 VARCHAR(4))
>
> INSERT INTO #TEST (PKID, Address1, zip5, zip4)
> VALUES('1','123 ST', '12345', '1234')
> INSERT INTO #TEST (PKID, Address1, zip5, zip4)
> VALUES('2','123 ST', '12345', '1234')
> INSERT INTO #TEST (PKID, Address1, zip5, zip4)
> VALUES('3','123 ST', '12345', '1234')
> INSERT INTO #TEST (PKID, Address1, zip5, zip4)
> VALUES('4','123 ST', '12345', '1234')
> INSERT INTO #TEST (PKID, Address1, zip5, zip4)
> VALUES('5','123 ST', '12345', '1234')
> INSERT INTO #TEST (PKID, Address1, zip5, zip4)
> VALUES('1','123 RD', '12345', '1234')
> INSERT INTO #TEST (PKID, Address1, zip5, zip4)
> VALUES('2','123 RD', '12345', '1234')
> INSERT INTO #TEST (PKID, Address1, zip5, zip4)
> VALUES('3','123 Rd', '12345', '1234')
> INSERT INTO #TEST (PKID, Address1, zip5, zip4)
> VALUES('4','123 RD', '12345', '1234')
> INSERT INTO #TEST (PKID, Address1, zip5, zip4)
> VALUES('5','123 RD', '12345', '1234')
> INSERT INTO #TEST (PKID, Address1, zip5, zip4)
> VALUES('1','123 AVE', '12345', '1234')
> INSERT INTO #TEST (PKID, Address1, zip5, zip4)
> VALUES('2','123 AVE', '12345', '1234')
> INSERT INTO #TEST (PKID, Address1, zip5, zip4)
> VALUES('3','123 AVE', '12345', '1234')
> INSERT INTO #TEST (PKID, Address1, zip5, zip4)
> VALUES('4','123 AVE', '12345', '1234')
> INSERT INTO #TEST (PKID, Address1, zip5, zip4)
> VALUES('5','123 AVE', '12345', '1234')
>
>
>
> On 1/26/07, Gustav Brock <Gustav at cactus.dk> wrote:
> > Hi John
> >
> > How about using First for the name and Group By for the address?
> >
> > /gustav
> >
> > >>> jwcolby at colbyconsulting.com 26-01-2007 16:01:06 >>>
> > I have a dataset where I have multiple addresses, multiple people living at
> > the same address.  "Address" is defined as "Address1,zip5,zip4".  Is it
> > possible to directly create a result set with just the first record at each
> > address?
> >
> > John W. Colby
> > Colby Consulting
> > www.ColbyConsulting.com
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>


-- 
-Francisco
http://sqlthis.blogspot.com | Tsql and More...