Access SQL 2007 - How Do I Return First Match For Each Item in a List

I am trying to create a query that will return the first matching record for each GlobalId from in a list.  In the table I usually have more than one record per GlobalID but only need to pull back the first record encountered per GlobalID match.

I have tried using TOP but it returns all records related to each global id as my query below is currently written.


SELECT TOP 25 *
FROM [Reconciliation-Working Copy]
WHERE GlobalID IN (SELECT Distinct GlobalId FROM [Reconciliation-Working Copy] WHERE GlobalID <> 'NULL')

Open in new window

NimBotAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

pssandhuCommented:
Can you post your table structure and some sample data?
P.
8080_DiverCommented:
And a definition of what you mean by "the first match"?  There is no guaranteed order for things when one is working with SQL unless one forces some sort of ordering.
NimBotAuthor Commented:
Sample of Table Structure is pasted below.  When the values are returned based upon GlobalId I only need one row per Global Id to be returned.  It does not matter if it is the first or second row per Global Id match is returned.




Site	Sys	LegId	MtmSource	MtmDate	MtmCurr	MtmExp	MtmHigh	MtmLow	UpdUserID	TradeId	GlobalId	Trade Type	ExternalRefID	Filepath	Comments
YZ	XXXX	123L.A	System	20080912	USD	5000			Fred	123L	1	Vanilla Swap		C:\	
YZ	XXXX	123L.B	System	20080912	USD	5000			Fred	123L	1	Vanilla Swap		C:\	
YZ	XXXX	456C.A	System	20080912	USD	-7000			Sam	456C	2	Vanilla Swap		C:\	
YZ	XXXX	456C.B	System	20080912	USD	-7000			Sam	456C	2	Vanilla Swap		C:\	
YZ	XXXX	678M.A	System	20080912	USD	12000			Harold	678M	3	Vanilla Swap		C:\	
YZ	XXXX	678M.B	System	20080912	USD	12000			Harold	678M	3	Vanilla Swap		C:\	

Open in new window

pssandhuCommented:
I cannot test this but please see if this works:


select r.*
From tablename r inner join ( select globalid, min(legid) Id from tablename group by globalid) t
On r.globalid = t.globalid and r.legid =t.Id

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
NimBotAuthor Commented:
Thanks.  The solution worked.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.