Help with Query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with Query

Author

Topic

Cpierswim
Starting Member

7 Posts

Posted - 2010-08-09 : 12:02:53

I have what is, for me at least, a rather complicated query.

I am a swim coach, and I'm working on a project that will display the top 10 times.

I have 3 Tables

(Table) Meets
-MeetID
-Meet Name
-Meet Date
-other meet info not necessary for the query

(Table) Swimmer
-SwimmerID
-Swimmer Last Name
-Swimmer First Name
-Swimmer Gender
-other info not necessary for the query

(Table) Swims
-Swim ID
-Meet ID
-Swimmer ID
-Age at time of swim
-Time of Swim (stored as an int)
-Stroke
-Distance
-Course of Swim (Either Long Course, or Short Course)
-other into not necesary for the query

I want the top 10 swims by Gender, Distance, Stroke, and Age at time of swim. (For example, the top 10 times swum in the 50 free in a Short Course pool by a girl between the ages of 9 and 10)

Result would be something like

Swimmer1lastname, Swimmer2firstname, time of swim, meet date, meet name

I've tried a bunch of stuff, and I get close, but never exactly what I need. A lot of my results are returning the top 10 swims, but will not return just the fastest time for a certain swimmer but all of their times so a single swimmer will occationally have all 10 of the top 10 times. So I figure I'm doing GROUP BY wrong. But when I use GROUP BY, I only get the time of the swim and the swimmer ID and nothing else.

I'm really new to SQL, so I need some help.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-09 : 12:20:39

Cpierswim, can you provide the DDL, DML and expected output? It makes it much easier if the information is in a format that we can execute. This link should help you prove that. Also, it'd be good to provide a query that you have tried that doesn't quite work.

Here is a link that will help prove DDL and DML in a consumable format:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Cpierswim
Starting Member

7 Posts

Posted - 2010-08-09 : 12:58:46

I'm assuming this is what you want. I'll split it up into 2 pots.

CREATE TABLE [dbo].[MEET](
[Meet] [int] IDENTITY(1,1) NOT NULL,
[MName] [nvarchar](45) NULL,
[Start] [datetime] NULL,
[End] [datetime] NULL,
[AgeUp] [datetime] NULL,
[Since] [datetime] NULL,
[Course] [nvarchar](2) NULL,
[Location] [nvarchar](45) NULL,
[Remarks] [nvarchar](50) NULL,
[IndCharge] [real] NULL,
[RelCharge] [real] NULL,
[SurCharge] [real] NULL,
[Type] [nvarchar](3) NULL,
[Sanction] [nvarchar](15) NULL,
[MaxIndEnt] [smallint] NULL,
[MaxRelEnt] [smallint] NULL,
[MaxEnt] [smallint] NULL,
[RestrictBest] [bit] NOT NULL,
[NonConform] [float] NULL,
[EnterAtQTime] [bit] NOT NULL,
[FacilityFee] [real] NULL,
[TeamFee] [real] NULL,
[Instructions] [nvarchar](250) NULL,
[MinAge] [smallint] NULL,
[EnforceQualifying] [bit] NOT NULL,
[Altitude] [smallint] NULL,
[EnforceSlowQtime] [bit] NOT NULL,
[BanNoTimes] [bit] NOT NULL,
[Lanes] [smallint] NULL,
[EvenOrOdd] [smallint] NULL,
[FastToSlow] [bit] NOT NULL,
[Masters] [bit] NOT NULL,
[ActiveFeeXMLSent] [bit] NOT NULL,
[MinAge10AndUnder] [smallint] NULL,
[SeedLanes] [nvarchar](16) NULL,
[DeadLine] [datetime] NULL,
[ActiveMeetID] [int] NULL,
CONSTRAINT [aaaaaMEET_PK] PRIMARY KEY NONCLUSTERED
(
[Meet] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Athlete](
[Athlete] [int] IDENTITY(1,1) NOT NULL,
[Team1] [int] NULL,
[Team2] [int] NULL,
[Team3] [int] NULL,
[Group] [nvarchar](3) NULL,
[SubGr] [nvarchar](3) NULL,
[Last] [nvarchar](20) NULL,
[First] [nvarchar](20) NULL,
[Initial] [nvarchar](1) NULL,
[Sex] [nvarchar](1) NULL,
[Birth] [datetime] NULL,
[Age] [smallint] NULL,
[Class] [nvarchar](3) NULL,
[ID_NO] [nvarchar](17) NULL,
[Citizen] [nvarchar](3) NULL,
[Inactive] [bit] NOT NULL,
[Pref] [nvarchar](20) NULL,
[Batch] [smallint] NULL,
[WMGroup] [nvarchar](3) NULL,
[WMSubGr] [nvarchar](3) NULL,
[theSort] [int] NULL,
[BCSSASwimmer] [nvarchar](2) NULL,
[BCSSADiver] [nvarchar](2) NULL,
[BCSSASyncro] [nvarchar](2) NULL,
[BCSSAPolo] [nvarchar](2) NULL,
[DateClubJoined] [datetime] NULL,
[DateGroupJoined] [datetime] NULL,
[DiveCertified] [bit] NOT NULL,
CONSTRAINT [aaaaaAthlete_PK] PRIMARY KEY NONCLUSTERED
(
[Athlete] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[RESULT](
[MEET] [int] NULL,
[ATHLETE] [int] NULL,
[I_R] [nvarchar](1) NULL,
[TEAM] [int] NULL,
[SCORE] [int] NULL,
[F_P] [nvarchar](1) NULL,
[SPLIT] [int] NULL,
[EX] [nvarchar](1) NULL,
[ORIGIN] [nvarchar](4) NULL,
[NT] [smallint] NULL,
[RESULT] [int] IDENTITY(1,1) NOT NULL,
[MISC] [nvarchar](1) NULL,
[AGE] [smallint] NULL,
[DISTANCE] [smallint] NULL,
[STROKE] [smallint] NULL,
[MTEVENT] [int] NULL,
[POINTS] [smallint] NULL,
[PLACE] [smallint] NULL,
[RANK] [smallint] NULL,
[TRANK] [smallint] NULL,
[COURSE] [nvarchar](1) NULL,
[REACTION] [nvarchar](5) NULL,
[DQCODE] [nvarchar](2) NULL,
[DQDESCRIPT] [nvarchar](90) NULL,
[DQCODESecondary] [nvarchar](2) NULL,
[DQDESCRIPTSecondary] [nvarchar](90) NULL,
CONSTRAINT [aaaaaRESULT_PK] PRIMARY KEY NONCLUSTERED
(
[RESULT] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Cpierswim
Starting Member

7 Posts

Posted - 2010-08-09 : 13:01:43

The next step (DML) is going to take me a while to create some test data because I can't put any real stuff up here because it is personal info, and I have to go to a dentist appt.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-09 : 13:30:18

Like Lamprey said, we need sample data to give you an exact working solution. But...give this a shot

SELECT TOP 10 
b.SwimmerLastName
, b.SwimmerFirstName
, a.TimeOfSwim
, c.MeetDate
, c.Meetname
FROM
(SELECT ROW_NUMBER() OVER (PARTITION BY SwimmerID ORDER BY TimeOfSwim DESC) as seq , * FROM Swims) a
INNER JOIN Swimmer b on a.SwimmerID = b.SwimmerID
INNER JOIN Meets c on a.MeetID = c.MeetID
WHERE a.seq = 1
AND a.Distance = 50
AND a.CourseOfSwim = 'Short'
AND b.SwimmerGender = 'Female'
AND a.Age between 9 and 10

Cpierswim
Starting Member

7 Posts

Posted - 2010-08-09 : 15:36:19

INSERT INTO ATHLETE (Athlete, Team1, Last, First, Sex, Inactive, DiveCertified)
VALUES (1, 1, "Botkins", "Sarah", "F", false, true)
INSERT INTO Athlete (Athlete, Team1, Last, First, Sex, Inactive, DiveCertified)
VALUES (2, 1, "Botkins", "Teddy", "M", false, true)
INSERT INTO Athlete (Athlete, Team1, Last, First, Sex, Inactive, DiveCertified)
VALUES (3, 1, "Stacy", "Julianne", "F", false, true)

INSERT INTO MEET (Meet, MName, Start, RestrictBest, EnterAtQTime, EnforceQualifying, EnforceSlowQtime,
BanNoTimes, FastToSlow, Masters, ActiveFeeXMLSent)
VALUES(1, "Test Meet", "10/8/2009", true, true, true, true, true, false, false)

INSERT INTO RESULT (Meet, Athlete, I_R, SCORE, F_P, RESULT, AGE, DISTANCE, STROKE,
COURSE)
VALUES (1, 1, "I", 2889, "F", 1, 10, 50, 1, "Y")
INSERT INTO RESULT (Meet, Athlete, I_R, SCORE, F_P, RESULT, AGE, DISTANCE, STROKE,
COURSE)
VALUES (1, 1, "I", 2903, "F", 1, 10, 50, 1, "Y")
INSERT INTO RESULT (Meet, Athlete, I_R, SCORE, F_P, RESULT, AGE, DISTANCE, STROKE,
COURSE)
VALUES (1, 2, "I", 2702, "F", 1, 10, 50, 1, "Y")
INSERT INTO RESULT (Meet, Athlete, I_R, SCORE, F_P, RESULT, AGE, DISTANCE, STROKE,
COURSE)
VALUES (1, 3, "I", 3002, "F", 1, 10, 50, 1, "Y")

Assuming Result.I_R = "I", Result.F_P="F", Result.Age= Between 9 and 10, Result.Disance=50, Result.stroke=1, Result.Course="Y", and Athlete.Sex="F" then the result should be

Athlete.Last Athlete.First Result.Score Meet.StartDate Meet.MName
Botkins Sarah 2889 10/8/09 Test Meet
Stacy Julianne 3002 10/8/09 Test Meet

Note that the primary keys are ATHLETE.ATHLETE, MEET.MEET, and RESULT.RESULT

Result.Score is the swim time as an int. Actual times there are converted after the database is given. (ie. Time from above are 0:28.89 and 0:30.02)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-09 : 16:17:40

Fixed upthe insert statements a bit so they will run (hopefully, they are accurate):

SET IDENTITY_INSERT ATHLETE ON
INSERT INTO ATHLETE (Athlete, Team1, Last, First, Sex, Inactive, DiveCertified)
VALUES (1, 1, 'Botkins', 'Sarah', 'F', 0, 1)
INSERT INTO Athlete (Athlete, Team1, Last, First, Sex, Inactive, DiveCertified)
VALUES (2, 1, 'Botkins', 'Teddy', 'M', 0, 1)
INSERT INTO Athlete (Athlete, Team1, Last, First, Sex, Inactive, DiveCertified)
VALUES (3, 1, 'Stacy', 'Julianne', 'F', 0, 1)
SET IDENTITY_INSERT ATHLETE OFF

SET IDENTITY_INSERT MEET ON
INSERT INTO MEET (Meet, MName, Start, RestrictBest, EnterAtQTime, EnforceQualifying, EnforceSlowQtime, 
BanNoTimes, FastToSlow, Masters, ActiveFeeXMLSent)
VALUES(1, 'Test Meet', '10/8/2009', 1, 1, 1, 1, 1, 0, 0, 0)
SET IDENTITY_INSERT MEET OFF

SET IDENTITY_INSERT RESULT ON
INSERT INTO RESULT (Meet, Athlete, I_R, SCORE, F_P, RESULT, AGE, DISTANCE, STROKE, 
COURSE)
VALUES (1, 1, 'I', 2889, 'F', 1, 10, 50, 1, 'Y')
INSERT INTO RESULT (Meet, Athlete, I_R, SCORE, F_P, RESULT, AGE, DISTANCE, STROKE, 
COURSE)
VALUES (1, 1, 'I', 2903, 'F', 2, 10, 50, 1, 'Y')
INSERT INTO RESULT (Meet, Athlete, I_R, SCORE, F_P, RESULT, AGE, DISTANCE, STROKE, 
COURSE)
VALUES (1, 2, 'I', 2702, 'F', 3, 10, 50, 1, 'Y')
INSERT INTO RESULT (Meet, Athlete, I_R, SCORE, F_P, RESULT, AGE, DISTANCE, STROKE, 
COURSE)
VALUES (1, 3, 'I', 3002, 'F', 4, 10, 50, 1, 'Y')
SET IDENTITY_INSERT RESULT OFF

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-09 : 16:17:59

And a query to get the results:

SELECT TOP 10
	*
FROM
	(
		SELECT 
			A.Last,
			A.First,
			R.Score,
			M.Start,
			M.MName,
			ROW_NUMBER() OVER (PARTITION BY A.Athlete ORDER BY R.Score) AS RowNum
		FROM 
			Athlete AS A
		INNER JOIN
			Result AS R
			ON A.Athlete = R.Athlete
		INNER JOIN
			Meet AS M
			ON M.Meet = R.Meet
		WHERE
			R.I_R = 'I' 
			AND R.F_P = 'F' 
			AND R.Age Between 9 and 10
			AND R.Distance = 50
			AND R.stroke = 1
			AND R.Course = 'Y'
			AND A.Sex = 'F'
	) AS T
WHERE 
	RowNum = 1
ORDER BY 
	Score ASC

Cpierswim
Starting Member

7 Posts

Posted - 2010-08-09 : 19:23:10

Tried it and got this:

The OVER SQL construct or statement is not supported.

I guess because I'm running Express 2008 R2. If I put it on a SQL Server 2005 database, should it work?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-08-10 : 10:50:01

OVER should work in SQL 2005+. Are you running in 2000 compatibility mode?

Cpierswim
Starting Member

7 Posts

Posted - 2010-08-10 : 11:17:58

I don't know (I'm really new to this) but I doubt it. I just installed with all the default settings.

The database is originally an Access database that I copied all of the information to an SQL Server 2008 Express R2 database. I'm thinking it might be easier right now to just copy everything to a new single table schema on the second database because I am just using it for the purpose of showing the top 10 swims and nothing else. However, in the future, I am probably going to want the 3 tables seperated.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-08-10 : 11:31:04

Can you run both these commans and post the version and the compatability level.

select @@version
exec sp_dbcmptlevel 'DatabaseName'

Cpierswim
Starting Member

7 Posts

Posted - 2010-08-10 : 12:35:51

That statement wouldn't run. So I realized the problem was probably that I was trying to run it in a Query wizard in Visual Studio. So I switch to Management Studio and got:

Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

Then I realzied that the previous select may work. This time it worked, and the result was correct.

Now I have to set out to figure out how that statement works.

Thanks for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources