Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 10 of 15 total |
SQL Help |
Mon, Oct 12 2015 7:11 PM | Permanent Link |
Lance Rasmussen CDE Software Team Elevate | I'm having a brain clog.
Table that has the following as part of the columns: Table X FirstName | LastName | Score1 | Score2 | TotalScore | Grade John | Doe | 101 | 102 | 203 | 10 Mary | Moe | 111 | 112 | 223 | 10 Herb | Garden | 121 | 122 | 243 | 9 Terry | Terrance | 131 | 132 | 263 | 10 John | Foe | 91 | 92 | 183 | 10 I'm trying to do a descending sort based on either score 1 or score 2 or total score within a certain grade, which isn't difficult. But what I'm trying to do is have a place column added, so that results show 1st, 2nd, 3rd, etc. So looking for the top total scores for 10th graders, I could do a: select FirstName, LastName, TotalScore where grade=10 order by TotalScore DESC Would give me: Terry Terrance 263 Mary Moe 223 John Doe 203 John Foe 183 I would like have a place number too, which would need to be calculated: 1 Terry Terrance 263 2 Mary Moe 223 3 John Doe 203 4 John Foe 183 Any suggestions? Thanks! Lance |
Mon, Oct 12 2015 8:10 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | On 13/10/2015 12:11 p.m., Lance Rasmussen wrote:
> > > select FirstName, LastName, TotalScore where grade=10 order by TotalScore DESC > Try:- SELECT FirstName, LastName, TotalScore, RUNSUM(1) WHERE Grade=10 ORDER BY TotalScore DESC Cheers Jeff |
Tue, Oct 13 2015 10:59 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Jeff,
Ahem.... SELECT FirstName, LastName, TotalScore, RUNSUM(1) AS Place WHERE Grade=10 ORDER BY TotalScore DESC There, fixed it. Tim Young Elevate Software www.elevatesoft.com |
Wed, Oct 21 2015 10:00 PM | Permanent Link |
Richard Harding Wise Nutrition Coaching | HI Lance, Jeff and Tim
If it is important to give students with the same SCORE the same PLACE ranking then you could try this. Otherwise, someone may get upset and disappointed at receiving a lower PLACE than someone with the same SCORE. SELECT FirstName, LastName, TotalScore, (SELECT COUNT(DISTINCT TotalScore) FROM X AS X2 WHERE X2.TotalScore >= X1.TotalScore) AS Place FROM X AS X1 WHERE Grade=10 GROUP BY ID ORDER BY TotalScore DESC Richard Harding |
Wed, Oct 21 2015 10:43 PM | Permanent Link |
Jeff Cook Aspect Systems Ltd | On 22/10/2015 3:00 p.m., Richard Harding wrote:
> HI Lance, Jeff and Tim > > If it is important to give students with the same SCORE the same PLACE ranking then you could try this. > Otherwise, someone may get upset and disappointed at receiving a lower PLACE than someone with the same SCORE. > > SELECT FirstName, LastName, TotalScore, > (SELECT COUNT(DISTINCT TotalScore) > FROM X AS X2 > WHERE X2.TotalScore >= X1.TotalScore) AS Place > FROM X AS X1 > WHERE Grade=10 > GROUP BY ID > ORDER BY TotalScore DESC > > Richard Harding > Cool! |
Thu, Oct 22 2015 3:20 AM | Permanent Link |
Malcolm Taylor | As this seems to be developing into a little competition, can I ramp it
up a notch or two? I need to rank sports results and (with my sport at least) it is customary to use the absolute place unless there is a tie. So, in the following example, note the absence of the places 3 and 4: 1 Malcolm 310.00 2 Bill 305.00 2 Tom 305.00 2 Roco 305.00 5 James 300.00 So using COUNT(DISTINCT..) does not do the job. Instead I need to use RUNSUM() but somehow override it for ties. When that little matter is solved, the next challenge I have to manage is guest competitors! Guests are to be given their absolute place (but also taking into account guests tied with guests!) but this must not affect the non-guest places. Yikes! Take this example: 1 Malcolm 310.00 2 Bill 305.00 2 Tom 305.00 4 Roco (guest) 305.00 4 James 300.00 At present I pop the required data into a temporary table with an index on the score then use table navigation to traverse it and insert the places based on the necessary running counts as well as managing the two types of potential ties. But a nice piece of SQL would be welcome ... Not holding breath! |
Thu, Oct 22 2015 6:41 AM | Permanent Link |
Richard Harding Wise Nutrition Coaching | Hi Malcolm
How about something like this? CREATE TABLE "Contacts" ( "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL, "FName" VARCHAR(24) COLLATE "ANSI", "Score" DECIMAL(19,2), "NonGuest" INTEGER -- 0=Guest & 1=NonGuest ) Create a view, removing the DISTINCT from the COUNT. CREATE VIEW "rkOrderByBalance" AS SELECT ID, FName, Score, NonGuest, (SELECT COUNT( Score* 100 + NonGuest) FROM Contacts C2 WHERE C2.Score* 100 + C2.NonGuest >= C1.Score* 100 + C1.NonGuest) AS Place FROM Contacts C1 ORDER BY ScoreDESC, NonGuest DESC This would give you, which is getting close. 1 Malcolm 310.00 3 Bill 305.00 3 Tom 305.00 4 Roco (guest) 305.00 5 James 300.00 Create another view that counts the number of people that are in the same position. CREATE VIEW "rkRankCount" AS SELECT Score, NonGuest, COUNT(*) AS PlaceCount FROM Contacts C1 GROUP BY Score, NonGuest Join the views together SELECT FName, Score, NonGuest, Place, PlaceCount, Place-PlaceCount+1 AS NewPlace FROM rkOrderByBalance AS R1 INNER JOIN rkRankCount AS R2 ON R1.Score = R2.Score AND R1.NonGuest = R2.NonGuest Which I think gives the answer. Maybe?? Richard |
Thu, Oct 22 2015 9:38 AM | Permanent Link |
Malcolm Taylor | Richard Harding wrote:
> Hi Malcolm > > How about something like this? > Wow, I could nearly have held my breath! When I get a few moment I will give that a whirl to see if it hits the spot, or gets close. ....... Of course, there is then the matter of overall ranking of prelims, semi-finals and finals. But if I can switch to SQL for finals the rest will be a simple(!) extension - ranking each qualifying stage starting with the appropriate, ID seed. |
Thu, Oct 22 2015 1:06 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Richard,
<< If it is important to give students with the same SCORE the same PLACE ranking then you could try this. Otherwise, someone may get upset and disappointed at receiving a lower PLACE than someone with the same SCORE. >> Nice. Tim Young Elevate Software www.elevatesoft.com |
Thu, Oct 22 2015 5:51 PM | Permanent Link |
Malcolm Taylor | Richard Harding wrote:
> > Which I think gives the answer. Maybe?? > Blimey .. it almost does! With a lot less code than my table navigation. And much safer for me to use Tim's SQL implementation than my navigational spaghetti. Only thing I have to do now is fix the error in my sample and tweak the solution accordingly. Roco (guest) should actually be placed 2 as that was his absolute rank. I will go over it again in the morning when the fog has cleared. Many thanks for your insight. |
Page 1 of 2 | Next Page » | |
Jump to Page: 1 2 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |