Whether this helps me, as Bill Clinton said, depends on what the meaning of
"is" is. I understand that I can substite, as you seem to be suggesting by
"is." My question is whether this is *efficient*. If I join the student tabl
e
to the attendance record table, which has one row for each student and schoo
l
day, and use Min(), then wouldn't I be telling SQL server to look, for each
student, at 180 identical copies of that student's name and find the
"minimum" one? Wouldn't that be inefficient?
--David
"Uri Dimant" wrote:
> David
> First() --SQL Server is MIN()
> Last() --SQL Server is MAX()
>
> "David Roodman" <DavidRoodman@.discussions.microsoft.com> wrote in message
> news:C1F7D9F9-D540-421A-9943-C07B4FC6295C@.microsoft.com...
>
>Perhaps you should tell us why you'd use FIRST and/or LAST at all. Is the
model properly normalised?
ML
http://milambda.blogspot.com/|||I think it is properly normalized... I have one table with a row for each
student. It contains student ID, name, and so on. I have another table with
a
row for each student and day, for attendance. The two are linked by student
ID. I want a view that gives me each student's name and the number of days
present. Actually, this is a made-up example, but should serve. I had though
t
the efficient way to do this in Jet was to GROUP BY the student ID, and
report the First() of the student name,and some aggregate statistics on the
student's attendance. I avoided grouping by student name or applying min() t
o
the student name because I thought those options were both more
computationally expensive. Is this untrue? Thank you.
"ML" wrote:
> Perhaps you should tell us why you'd use FIRST and/or LAST at all. Is the
> model properly normalised?
>
> ML
> --
> http://milambda.blogspot.com/|||To amplify my previous explanation:
It says "For greater speed, use Group By on as few fields as possible. As an
alternative, use the First function where appropriate."
at
http://msdn.microsoft.com/library/d...ter
S.asp|||> To amplify my previous explanation:
> It says "For greater speed, use Group By on as few fields as possible. As
> an
> alternative, use the First function where appropriate."
There is no FIRST() in SQL Server, so what MSDN happens to say about Jet is
completely irrelevant here.
Are you using SQL Server or Jet?|||>I think it is properly normalized... I have one table with a row for each
> student. It contains student ID, name, and so on. I have another table
> with a
> row for each student and day, for attendance. The two are linked by
> student
> ID. I want a view that gives me each student's name and the number of days
> present. Actually, this is a made-up example, but should serve. I had
> thought
> the efficient way to do this in Jet
Jet? What are you doing in a SQL Server group?
Anyway, you don't have any DDL, so I'll make some up for you.
CREATE TABLE dbo.Students
(
StudentID INT PRIMARY KEY, -- generated from where?
FullName VARCHAR(32)
)
GO
CREATE TABLE dbo.StudentAttendance
(
StudentID INT FOREIGN KEY
REFERENCES dbo.Students(StudentID),
dt SMALLDATETIME,
Present INT NOT NULL DEFAULT 1
)
GO
SET NOCOUNT ON;
INSERT dbo.Students SELECT 1, 'Johnny Keener';
INSERT dbo.Students SELECT 2, 'Molly NiceGirl';
INSERT dbo.Students SELECT 3, 'Trish WithSTD';
INSERT dbo.Students SELECT 4, 'Jimmy BadApple';
INSERT dbo.Students SELECT 5, 'New Kid';
GO
INSERT dbo.StudentAttendance SELECT 1, '20060101', 1;
INSERT dbo.StudentAttendance SELECT 1, '20060102', 1;
INSERT dbo.StudentAttendance SELECT 1, '20060103', 1;
INSERT dbo.StudentAttendance SELECT 2, '20060101', 1;
INSERT dbo.StudentAttendance SELECT 2, '20060102', 1;
INSERT dbo.StudentAttendance SELECT 2, '20060103', 1;
INSERT dbo.StudentAttendance SELECT 3, '20060101', 0;
INSERT dbo.StudentAttendance SELECT 3, '20060102', 1;
INSERT dbo.StudentAttendance SELECT 3, '20060103', 0;
INSERT dbo.StudentAttendance SELECT 4, '20060101', 0;
INSERT dbo.StudentAttendance SELECT 4, '20060102', 0;
INSERT dbo.StudentAttendance SELECT 4, '20060103', 0;
GO
SELECT s.StudentID,
s.FullName,
DaysPresent = COALESCE(a.DaysPresent, 0)
FROM
dbo.Students s
LEFT OUTER JOIN
(
SELECT StudentID,
DaysPresent = SUM(Present)
FROM dbo.StudentAttendance
GROUP BY StudentID
) a
ON
s.StudentID = a.StudentID;
GO
DROP TABLE dbo.StudentAttendance;
DROP TABLE dbo.Students;
I don't see why FIRST() or LAST() would make sense in any of this, even in
Jet.
A|||From my first post: "I am just moving a database from Jet and learning SQL
Server as I go."
Am I not being clear? It seems that people are reading too fast but trying
to be helpful. I was doing what is recommended practice in Jet; the
underlying concern that motivated that advice from MSDN still stands,
seemingly; and I am wondering what is the efficient way to handle it in SQL
Server. "Efficient" for the computer, not me.
