July 19, 2016 at 3:50 am
Hi,
I have 2 tables. Please see the attached screen shot for data. I need to join these 2 tables with
schoolID and pick top 3 students of each school and display the out put as shown in the picture.
I need a T SQL Query for this.
1. School
2. Student
I have provided table and data scripts an Output structure below.
USE [TEST]
GO
/****** Object: Table [dbo].[School] Script Date: 7/19/2016 4:29:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[School](
[SchoolID] [int] NULL,
[SchoolName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Student] Script Date: 7/19/2016 4:29:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student](
[SchoolID] [int] NULL,
[StudentID] [int] NULL,
[Studentkey] [nvarchar](50) NULL,
[StudentName] [nvarchar](50) NULL,
[NetworkID] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[School] ([SchoolID], [SchoolName]) VALUES (1, N'St John')
INSERT [dbo].[School] ([SchoolID], [SchoolName]) VALUES (2, N'St Xavier')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 1, N'125', N'A', N'12 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 1, N'125', N'A', N'13 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 1, N'125', N'A', N'14 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 2, N'225', N'B', N'19 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 2, N'225', N'B', N'21 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 2, N'225', N'B', N'24 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 3, N'325', N'C', N'45 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 3, N'325', N'C', N'34 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 3, N'325', N'C', N'35 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (2, 1, N'1125', N'A2', N'78 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (2, 1, N'1125', N'A2', N'87 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (2, 1, N'1125', N'A2', N'45 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (2, 2, N'2225', N'B2', N'12 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (2, 2, N'2225', N'B2', N'13 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (2, 3, N'3325', N'C2', N'96 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (2, 3, N'3325', N'C2', N'48 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (2, 3, N'3325', N'C2', N'49 ')
[/Code]
========== ============= ============ ============ ============ ============== ============== ==============
SchoolID School Name StudentID1 StudentID2 StudentID3 StudentName1 StudentName2 StudentName3
========== ============= ============ ============ ============ ============== ============== ==============
1 St John 125 225 325 A B C
2 St Xavier 1125 2225 3325 A2 B2 C2
========== ============= ============ ============ ============ ============== ============== ==============
Thanks
Amirtharaj J
July 19, 2016 at 4:20 am
please read this and then repost.
https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/
and why have you got duplicate identical rows in the student table?
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 19, 2016 at 10:46 am
amirtharaj.j (7/19/2016)
Hi,I have 2 tables. Please see the attached screen shot for data. I need to join these 2 tables with
schoolID and pick top 3 students of each school and display the out put as shown in the picture.
I need a T SQL Query for this.
1. School
2. Student
I have provided table and data scripts an Output structure below.
USE [TEST]
GO
/****** Object: Table [dbo].[School] Script Date: 7/19/2016 4:29:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[School](
[SchoolID] [int] NULL,
[SchoolName] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Student] Script Date: 7/19/2016 4:29:40 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Student](
[SchoolID] [int] NULL,
[StudentID] [int] NULL,
[Studentkey] [nvarchar](50) NULL,
[StudentName] [nvarchar](50) NULL,
[NetworkID] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[School] ([SchoolID], [SchoolName]) VALUES (1, N'St John')
INSERT [dbo].[School] ([SchoolID], [SchoolName]) VALUES (2, N'St Xavier')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 1, N'125', N'A', N'12 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 1, N'125', N'A', N'13 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 1, N'125', N'A', N'14 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 2, N'225', N'B', N'19 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 2, N'225', N'B', N'21 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 2, N'225', N'B', N'24 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 3, N'325', N'C', N'45 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 3, N'325', N'C', N'34 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (1, 3, N'325', N'C', N'35 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (2, 1, N'1125', N'A2', N'78 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (2, 1, N'1125', N'A2', N'87 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (2, 1, N'1125', N'A2', N'45 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (2, 2, N'2225', N'B2', N'12 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (2, 2, N'2225', N'B2', N'13 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (2, 3, N'3325', N'C2', N'96 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (2, 3, N'3325', N'C2', N'48 ')
INSERT [dbo].[Student] ([SchoolID], [StudentID], [Studentkey], [StudentName], [NetworkID]) VALUES (2, 3, N'3325', N'C2', N'49 ')
[/Code]
========== ============= ============ ============ ============ ============== ============== ==============
SchoolID School Name StudentID1 StudentID2 StudentID3 StudentName1 StudentName2 StudentName3
========== ============= ============ ============ ============ ============== ============== ==============
1 St John 125 225 325 A B C
2 St Xavier 1125 2225 3325 A2 B2 C2
========== ============= ============ ============ ============ ============== ============== ==============
Thanks
Amirtharaj J
thanks for the setup...please describe how you want to determine the "top 3" students?
maybe useful if you expand your sample data with more students.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 19, 2016 at 9:56 pm
Thanks Livingston . I need Top 3 based on below condition
ORDER BY StudentKey ASC;
July 19, 2016 at 11:36 pm
Here's a good article to read that you can adapt to answer your question:
http://dba.stackexchange.com/questions/86415/retrieving-n-rows-per-group
At least then you can give it a try and report back if you have problems. Helping people is one thing, doing their work for them is another.
For grins, I tried doing this against AdventureWorks... note the correlations (joins) between the inner and outer queries. "soh" refers to the SalesOrderHeader and "ca" refers to the cross-applied top values query.
use AdventureWorks2016CTP3;
GO
use AdventureWorks2016CTP3;
GO
SELECT soh.SalesOrderID
, soh.OrderDate
, soh.SalesOrderNumber
, soh.CustomerID
, ca.ProductID
, ca.OrderQty
, ca.LineTotal
FROM Sales.SalesOrderHeader soh
CROSS APPLY (SELECT TOP 3 *
FROM Sales.SalesOrderDetail
WHERE Sales.SalesOrderDetail.SalesOrderID = soh.SalesOrderID) ca
WHERE soh.SalesOrderID<43663;
July 20, 2016 at 2:22 am
Using Cross Apply I am able to bring as 3 rows, instead of 1. For now the solution is ok.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply