• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 131
  • Last Modified:

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
0
amandajdarlow
Asked:
amandajdarlow
  • 3
  • 3
1 Solution
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now