473,903 Members | 4,060 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

SELECT TOP 3 Within a GROUP BY

49 New Member
Here is what I have for a report that selects the top 3 winners:

Expand|Select|Wrap|Line Numbers
  1. SELECT Dupe.Class, Dupe.Score, Dupe.ID, Dupe.FName, Dupe.LName, Dupe.Year, Dupe.Make, Dupe.Model, Dupe.Judge
  2. FROM Data AS Dupe
  3. WHERE (((Dupe.Score)<>0 And (Dupe.Score) In (SELECT TOP 3 Data.Score FROM Data WHERE (((Data.Class)=Dupe.[Class])) ORDER BY Data.Score DESC)))
  4. ORDER BY Dupe.Class, Dupe.Score DESC , Dupe.ID;
  5.  
I need to add a filter in the select top 3 where if the record is disqualified it will not show up and select the next top 3.

Also a filter that if the record is marked as 'best of' it will not show up in the top 3 and select the next top 3.

Data.DSQ is True / False
Data.BestOf is numeric value.

So I want to filter out Data.DSQ = True and Data.BestOf > 0 from the Select Top 3 records.

I have tried this many ways and I can't figure it out. The problem I have is that I can filter these out, but it will not just skip them: it does not continue to select the next records to total a Top 3.

ie: if 3 records and two are disqualified, I only return 1 record and not the next in the top 3 list.
Aug 16 '11 #1
27 5998
patjones
931 Recognized Expert Contributor
I think the problem lies in the WHERE clause in the sub-query. I don't think the alias 'Dupe' should appear in there, and rather that's the spot to filter out your disqualificatio ns and best of records.

So with that said, maybe something like this?

Expand|Select|Wrap|Line Numbers
  1. SELECT Dupe.Class, 
  2.        Dupe.Score, 
  3.        Dupe.ID, 
  4.        Dupe.FName, 
  5.        Dupe.LName, 
  6.        Dupe.Year, 
  7.        Dupe.Make, 
  8.        Dupe.Model, 
  9.        Dupe.Judge
  10. FROM Data AS Dupe
  11. WHERE Dupe.Score <> 0 AND Dupe.Score IN (SELECT TOP 3 Data.Score 
  12.                                          FROM Data 
  13.                                          WHERE Data.DSQ <> TRUE AND Data.BestOf = 0
  14.                                          ORDER BY Data.Score DESC)
  15. ORDER BY Dupe.Class, Dupe.Score DESC , Dupe.ID;
  16.  

I'd also point out that it's not really necessary to alias the table in this situation, unless you have some plans for it outside the context of your question that would require aliasing. So I left it as is.
Aug 16 '11 #2
Rabbit
12,516 Recognized Expert Moderator MVP
Actually, they will need the alias because the subquery is filtering on class from the parent query.
Aug 16 '11 #3
patjones
931 Recognized Expert Contributor
Ah, right...it's a correlated sub-query! So this may change my advice?
Aug 16 '11 #4
Rabbit
12,516 Recognized Expert Moderator MVP
No, the SQL is fine, they'll just need to add back in the link.

Edit: In the SQL, it should be >0 and not =0.
Aug 16 '11 #5
Daniel Yantis
49 New Member
Well.... I'm confused.
Your SQL does not filter out disquaified even after I make Rabbit's edit.

This is what I have as a fresh working statement:
Expand|Select|Wrap|Line Numbers
  1. SELECT Dupe.Class, Dupe.Score, Dupe.ID, Dupe.FName, Dupe.LName, Dupe.Year, Dupe.Make, Dupe.Model, Dupe.Judge
  2. FROM Data AS Dupe
  3. WHERE (((Dupe.Score)<>0 And (Dupe.Score)
  4.   In (SELECT TOP 3 Data.Score
  5.       FROM Data
  6.       WHERE (((Data.Class)=Dupe.[Class]))
  7.       ORDER BY Data.Score DESC)))
  8. ORDER BY Dupe.Class, Dupe.Score DESC , Dupe.ID;
  9.  
And this is what I think you want it to be:
Expand|Select|Wrap|Line Numbers
  1. SELECT Dupe.Class, Dupe.Score, Dupe.ID, Dupe.FName, Dupe.LName, Dupe.Year, Dupe.Make, Dupe.Model, Dupe.Judge, Dupe.DSQ, Dupe.BestOf
  2. FROM Data AS Dupe
  3. WHERE (((Dupe.Score)<>0 And (Dupe.Score)
  4.   In (SELECT TOP 3 Data.Score
  5.       FROM Data
  6.       WHERE (((Data.Class)=Dupe.[Class] AND (Data.DSQ)<>True AND (data.BestOf)=0))
  7.       ORDER BY Data.Score DESC)))
  8. ORDER BY Dupe.Class, Dupe.Score DESC , Dupe.ID;
  9.  
Aug 17 '11 #6
Daniel Yantis
49 New Member
I should also say that Data.DSQ is a Yes/No Check Box in the form and in the table.
Aug 17 '11 #7
patjones
931 Recognized Expert Contributor
OK, that's an important piece of information. Instead of putting Data.DSQ <> TRUE, put Data.DSQ = 0 and see if that changes things.

Pat
Aug 17 '11 #8
NeoPa
32,584 Recognized Expert Moderator MVP
I could redo the WHERE clause on line #6 of the suggested SQL, but I don't see where what is posted would be expected to fail :
Expand|Select|Wrap|Line Numbers
  1.       WHERE (Data.Class=Dupe.Class)
  2.         AND (NOT Data.DSQ)
  3.         AND (Data.BestOf=0)
Perhaps you could post some example data of where this (the latter set of SQL you posted - the one you think we want it to be) is not working as expected/requested. I must admit I can't imagine what's going wrong as the SQL looks about right to me.
Aug 17 '11 #9
NeoPa
32,584 Recognized Expert Moderator MVP
ZeppHead80:
Instead of putting Data.DSQ <> TRUE, put ...
I absolutely agree with Pat's point. You should never, ever compare a boolean value or variable to TRUE if you want to determine whether it is TRUE or FALSE. It's stored as an integer and all non-zero integers are treated as TRUE for boolean purposes, yet only -1 is actually equal to the value TRUE. Hence comparison with TRUE is a misleading comparison and (obviously) can give misleading results.
Aug 17 '11 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

2
3413
by: nanookfan | last post by:
Hi all, I'm having a bizarre problem converting XML files to HTML using an XSLT. The problem is only occuring in my Netscape 7.0 browser. What makes it more bizarre is that it is only happening when I put my XML files and the .xsl files on my ISP's system for my home page. If I try to open the XML files in Netscape 7.0 on my own machine (ie, not on the ISP's system), the pages convert file and the result is displayed in HTML.
8
5675
by: Dominic Tocci | last post by:
I'm searching for a way to use window.open on my web page to open a window in firefox that allows the sidebars to work (bookmarks, history, etc). When I use the following: var popWindow=window.open('http://www.yahoo.com','','width=600,height=400,toolbar=1,location=1,menubar=1,resizable=1,titlebar=1,directories=1,status=1,scrollbars=1'); the sidebars are disabled. I click on the buttons for bookmarks and history and they do nothing. I...
3
1690
by: Leslie A Rhorer | last post by:
Hello all, I am quite new to HTML, and I have created a number of pages related to a project on which I am working. Several of the pages look just the way I want with the default font settings in IE and Mozilla in Windows XP, but they jumble up a bit if a larger font is used, as many of the readers of this application do, or if I selct a useable font on my Linux system. In other words, I need the bottom table (OObj2) to move up and...
2
2158
by: news.symantec.com | last post by:
Hi All, I'm trying to install DB2 Enterprise Edition 8.1 (partitioned installation) onto a clean Win 2k SP4 advanced server box. The problem is the partitioned installation requires a "Domain User" account for the partition (at least that is what it tells me at some point during the install) whereas a non-partitioned installation doesn't need one. Whenever I try to install DB2 logged in as a domain admin the installation tells me that...
1
2679
by: Methven | last post by:
File - Get External Data - Import - Text Type - Advanced - Specs PROBLEM Can anyone assist me with a problem ? When attempting to specify an import specification using ; File - Get External Data - Import - Text Type - Advanced - Specs Is it possible to specify a Spec layout, save it and then - at a later point - amend the Spec ?
2
2495
by: Mike Sweetman | last post by:
I have a form Form1 which when the Advanced Filter/Sort is used creates a form(maybe) with a title 'Form1Filter1 : Filter'. When I apply the filter to Form1 it is applied, but the value of Form1.Filter is ''. How do I obtain the Advanced Filter/Sort criteria as a string ? I cannot reference the object 'Form1Filter1 : Filter' by using Form1Filter1.
1
2469
by: Parv | last post by:
I am trying to impersonate user to some other system using userName,domainName,password in C#. My Code is working fine if i am working on Windows 2000 professional after assigning current user "Act as part of Operating system" permission. But when i tried to do the same on Windows 2000 advanced server after assigning current user i.e. administrator or anyone else "Act as part of Operating system" permission it gives me error "A required...
3
2590
omerbutt
by: omerbutt | last post by:
hi there i have downloaded a prototype tooltip from http://www.nickstakenburg.com/projects/prototip/ the logic it uses is to call the script after creating the <div> for example i am using the ajax method <html> <body> <div id='my_div'> </div>
2
3022
by: swethak | last post by:
hi , i write the code in .htm file. It is in cgi-bin/searches/one.htm.In that i write a form submitting and validations.But validations are not worked in that .htm file. I used the same code in my local system that validations work.plz tell that whats the problem in that. Here is my code <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <html...
0
9997
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
11279
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10872
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10981
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9675
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
7205
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
4725
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4307
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3323
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.