[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

SQL statement - want only distinct top 3

Posted on 2010-01-11
4
Medium Priority
?
359 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
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

611 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