[AccessD] Top1

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