?
Solved

SQL statement - want only distinct top 3

Posted on 2010-01-11
4
Medium Priority
?
349 Views
Last Modified: 2012-05-08
Hi,

Attached is a SQL code from a query that I have built.  At the moment the query is returning all records and bringing back duplicates on VenName (which is populated via a join to VenId)

The query brings back Venues and calculation of the different ratings for that Venue.  How the query is currently written, it brings back all data so I might get Venue A appearing in the top 3 twice, which statistically it is correct, but I don't want that.  I want the Top 3 distinct venues brought back, based on the calculation.

What I want is to ONLY bring back the TOP 3 and for their to be NO duplicates of Venue Name within that.

I hope that makes sense
SELECT TOP 3 tbl_Venue.VenName, tbl_Venue.VenId, [Access]+[Fishing]+[Pegs]+[Clean]+[Facilities] AS FTotal, tbl_UserRatings.Access, tbl_UserRatings.Fishing, tbl_UserRatings.Pegs, tbl_UserRatings.Facilities, tbl_UserRatings.Clean, (([Access]+[Fishing]+[Pegs]+[Clean]+[Facilities])/40)*100 AS FPercent, tbl_Users.Username, tbl_UserRatings.RatedOn, tbl_UserRatings.RatingId, tbl_UserRatings.Title, tbl_UserRatings.Review
FROM tbl_Users INNER JOIN (tbl_Venue RIGHT JOIN tbl_UserRatings ON tbl_Venue.VenId = tbl_UserRatings.VenueId) ON tbl_Users.UserId = tbl_UserRatings.RatedBy
GROUP BY tbl_Venue.VenName, tbl_Venue.VenId, tbl_UserRatings.Access, tbl_UserRatings.Fishing, tbl_UserRatings.Pegs, tbl_UserRatings.Facilities, tbl_UserRatings.Clean, (([Access]+[Fishing]+[Pegs]+[Clean]+[Facilities])/40)*100, tbl_Users.Username, tbl_UserRatings.RatedOn, tbl_UserRatings.RatingId, tbl_UserRatings.Title, tbl_UserRatings.Review, tbl_UserRatings.RatedBy
ORDER BY (([Access]+[Fishing]+[Pegs]+[Clean]+[Facilities])/40)*100 DESC;

Open in new window

0
Comment
Question by:wobbled
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 26286531
Assuming that vendorId is unique among all vendors (PrimaryKey) and vendorName is not (different vendors can have the same name), venId should come before venName in both the select and group by clauses.
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 26287014
Save the below query as YourQuery.

SELECT tbl_Venue.VenName,
       tbl_Venue.VenId,
       [Access]+[Fishing]+[Pegs]+[Clean]+[Facilities] AS FTotal,
       tbl_UserRatings.Access,
       tbl_UserRatings.Fishing,
       tbl_UserRatings.Pegs,
       tbl_UserRatings.Facilities,
       tbl_UserRatings.Clean,
       (([Access]+[Fishing]+[Pegs]+[Clean]+[Facilities])/40)*100 AS FPercent,
       tbl_Users.Username,
       tbl_UserRatings.RatedOn,
       tbl_UserRatings.RatingId,
       tbl_UserRatings.Title,
       tbl_UserRatings.Review
  FROM tbl_Users
 INNER JOIN (tbl_Venue RIGHT JOIN tbl_UserRatings ON tbl_Venue.VenId = tbl_UserRatings.VenueId) ON tbl_Users.UserId = tbl_UserRatings.RatedBy
 GROUP BY tbl_Venue.VenName, tbl_Venue.VenId, tbl_UserRatings.Access, tbl_UserRatings.Fishing, tbl_UserRatings.Pegs, tbl_UserRatings.Facilities, tbl_UserRatings.Clean, (([Access]+[Fishing]+[Pegs]+[Clean]+[Facilities])/40)*100, tbl_Users.Username, tbl_UserRatings.RatedOn, tbl_UserRatings.RatingId, tbl_UserRatings.Title, tbl_UserRatings.Review, tbl_UserRatings.RatedBy
 ORDER BY (([Access]+[Fishing]+[Pegs]+[Clean]+[Facilities])/40)*100 DESC;

Use YourQuery and create another query like this.

SELECT TOP 3 *
  FROM YourQuery AS t1
 WHERE FPercent = (SELECT MAX(FPercent) FROM YourQyery AS t2 WHERE t1.VenName = t2.VenName)
 ORDER BY FPercent DESC
 
Let me know if you face any issues in this.
0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 26287278
Use one query to score all of the user ratings:
qry_ScoreUserRatings:

SELECT tbl_UserRatings.VenueID, [Access]+[Fishing]+[Pegs]+[Clean]+[Facilities] AS FTotal, (([Access]+[Fishing]+[Pegs]+[Clean]+[Facilities])/50)*100 AS FPercent
FROM tbl_UserRatings
ORDER BY (([Clean]+[Fishing]+[Access]+[Pegs]+[Facilities])/50)*100 DESC;

Then, choose the choose the three venues with the highest rating (ignoring that 1 venue may hold multiple high ratings):

qry_ThreeBestVenues
SELECT TOP 3 qry_ScoreUserRatings.VenueID, Max(qry_ScoreUserRatings.FPercent) AS MaxOfFPercent
FROM qry_ScoreUserRatingsINNER JOIN tbl_Venue ON qry_ScoreUserRatings.VenueID=tbl_Venue.VenID
GROUP BY qry_ScoreUserRatings.VenueID
ORDER BY Max(qry_ScoreUserRatings.FPercent) DESC;

Then, you can use qry_ThreeBestVenues as the base for a query that connects VenueName, and all the other details you want. Or, change the display control of VenueID to be a lookup on tbl_Venue with two columns, VenID with width 0 and VenName with width (default).

I changed your 40 to 50 since I figured you have a 5 category 10 point scale. You also may consider changing the Max in the second query to Avg, since one user may just give a lot of high scores.

HTH,
pT72


0
 
LVL 17

Author Closing Comment

by:wobbled
ID: 31675654
Excellent - Thanks for your help.  This did the job
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question