SQL Query to mimic MS Access First

Looking at the attaced screenshot can anyone suggest how I can create a SQL Server stored procedure or view which picks out the record with the lowest TagCategorySortId(last column) for each SNT SessionNotesId?

I am no SQL Server guru and I would like to know the 'correct' and 'best' way to do this.

Doc1.docx
amandajdarlowAsked:
Who is Participating?
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.

ZberteocCommented:
SELECT  TOP 1 * from ... WHERE SessionNotesId=<some_id> ORDER BY TagCategorySortId

However I noticed that you have 2 rows with SessionNotesId=45803 and TagCategorySortId=1. Which one you choose?
0
amandajdarlowAuthor Commented:
I need to be able to select all the first records at the same time rather than by SessionNotesId.

In this case " However I noticed that you have 2 rows with SessionNotesId=45803 and TagCategorySortId=1. Which one you choose?  "I am happy to simply select the first or both, it doesn't matter for now.
0
ZberteocCommented:
Can you copy the select statement from the picture in text and post it here? I need to work on it.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

amandajdarlowAuthor Commented:
SELECT        dbo.tblSessionNoteTags.SNTSessionNotesId, dbo.tblTag.TagId, dbo.tblTagCategory.TagCategorySort
FROM            dbo.tblSessionNoteTags INNER JOIN
                         dbo.tblTag ON dbo.tblSessionNoteTags.SNTTagId = dbo.tblTag.TagId INNER JOIN
                         dbo.tblTagCategory ON dbo.tblTag.TagCategoryID = dbo.tblTagCategory.TagCategoryId
WHERE        (dbo.tblTagCategory.TagCategorySort IS NOT NULL)
ORDER BY dbo.tblSessionNoteTags.SNTSessionNotesId, dbo.tblTagCategory.TagCategorySort
0
ZberteocCommented:
Try this:
SELECT
	dbo.tblSessionNoteTags.SNTSessionNotesId, 
	dbo.tblTag.TagId, 
	dbo.tblTagCategory.TagCategorySort
FROM            
	dbo.tblSessionNoteTags 
	INNER JOIN dbo.tblTag 
		ON dbo.tblSessionNoteTags.SNTTagId = dbo.tblTag.TagId 
	INNER JOIN dbo.tblTagCategory 
		ON dbo.tblTag.TagCategoryID = dbo.tblTagCategory.TagCategoryId 
	INNER JOIN
	(
		SELECT
			dbo.tblSessionNoteTags.SNTSessionNotesId, 
			min(dbo.tblTagCategory.TagCategorySort) as TagCategorySort
		FROM            
			dbo.tblSessionNoteTags 
			INNER JOIN dbo.tblTag 
				ON dbo.tblSessionNoteTags.SNTTagId = dbo.tblTag.TagId 
			INNER JOIN dbo.tblTagCategory 
				ON dbo.tblTag.TagCategoryID = dbo.tblTagCategory.TagCategoryId
		WHERE        
			dbo.tblTagCategory.TagCategorySort IS NOT NULL
		GROUP BY
			dbo.tblSessionNoteTags.SNTSessionNotesId, 
	) min_sorts
		ON	min_sorts.SNTSessionNotesId=dbo.tblSessionNoteTags.SNTSessionNotesId
		AND	min_sorts.TagCategorySort=tblTagCategory.TagCategorySort
ORDER BY 
	dbo.tblSessionNoteTags.SNTSessionNotesId, dbo.tblTagCategory.TagCategorySort

Open in new window

0

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
amandajdarlowAuthor Commented:
Brilliant works perfectly.  Thank you very  much.  I shall study the technique later.
0
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
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.