Preventing GroupBy clause

I had a question similar to this before and can't seem to make this one
work.
I have the following:
Select
ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
from logon l join Applicant a on (l.email=a.email) where l.email =
'tfs@.dlink.com'
This works fine if I don't have the max function, but it can give me
multiple responses so I just want the last ApplicantID. This also works if
I put the password, email,firstName and LastName in a Groupby clause. Can I
change the max statement to prevent having to use the Groupby Clause.
Thanks,
TomIs there any special reason you'd rather not use a GROUP BY clause?
Any way, you can use:
SELECT TOP 1 <col_list>
FROM <table>
ORDER BY <sort_list>
BG, SQL Server MVP
www.SolidQualityLearning.com
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:u7J2wekAFHA.2932@.TK2MSFTNGP10.phx.gbl...
>I had a question similar to this before and can't seem to make this one
>work.
> I have the following:
> Select
> ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
> from logon l join Applicant a on (l.email=a.email) where l.email =
> 'tfs@.dlink.com'
> This works fine if I don't have the max function, but it can give me
> multiple responses so I just want the last ApplicantID. This also works
> if I put the password, email,firstName and LastName in a Groupby clause.
> Can I change the max statement to prevent having to use the Groupby
> Clause.
> Thanks,
> Tom
>|||"Itzik Ben-Gan" <itzik@.REMOVETHIS.SolidQualityLearning.com> wrote in message
news:O1mgWnkAFHA.2792@.TK2MSFTNGP15.phx.gbl...
> Is there any special reason you'd rather not use a GROUP BY clause?
Mainly, because it is just something more for SQL to do, when all I want is
the Max Applicant.

> Any way, you can use:
> SELECT TOP 1 <col_list>
> FROM <table>
> ORDER BY <sort_list>
Same problem as above - would like to just say Max instead of having to sort
it.
It may not be much different, but I assume that doing a sort or group is a
little more of a hit to the engine that getting the max number.
Thanks,
Tom
> --
> BG, SQL Server MVP
> www.SolidQualityLearning.com
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:u7J2wekAFHA.2932@.TK2MSFTNGP10.phx.gbl...
>|||> It may not be much different, but I assume that doing a sort or group is a
> little more of a hit to the engine that getting the max number.
Well, did you test your theory? We have the ability to view the plans,
stress test our apps, and time our code. Why are we relying on assumptions
to make our decisions for us?|||<snip>
> Same problem as above - would like to just say Max instead of having to so
rt
> it.
> It may not be much different, but I assume that doing a sort or group is a
> little more of a hit to the engine that getting the max number.
So how is SQL-Server supposed to determine the Max ApplicantID without
some way of comparing the different ApplicantIDs? Should SQL-Server just
guess it? In that case, you can leave out the ORDER BY clause, and you
will get 'any' ApplicantID.
Otherwise, just use the technology (i.e. use a GROUP BY clause) and
trust the product, or do some actual performance testing instead of
asking the impossible. Chances are that you would even see a significant
(measurable) performance difference between the different methods,
unless you're using a big table.
Gert-Jan|||"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41F567CA.CC06163B@.toomuchspamalready.nl...
> <snip>
> So how is SQL-Server supposed to determine the Max ApplicantID without
> some way of comparing the different ApplicantIDs? Should SQL-Server just
> guess it? In that case, you can leave out the ORDER BY clause, and you
> will get 'any' ApplicantID.
>
No, I was trying to use the following:
Select
ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
from logon l join Applicant a on (l.email=a.email) where l.email =
'tfs@.dlink.com'
which does work fine if I take all fields out. I want the max ApplicantID,
because it will be the last ApplicantID assigned for this email address.
Tom
> Otherwise, just use the technology (i.e. use a GROUP BY clause) and
> trust the product, or do some actual performance testing instead of
> asking the impossible. Chances are that you would even see a significant
> (measurable) performance difference between the different methods,
> unless you're using a big table.
> Gert-Jan|||> No, I was trying to use the following:
> Select
> ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
> from logon l join Applicant a on (l.email=a.email) where l.email =
> 'tfs@.dlink.com'
> which does work fine if I take all fields out. I want the max
ApplicantID,
> because it will be the last ApplicantID assigned for this email address.
You need to get the MAX from a subquery. Also note that SQL Server will
have no idea if you want the MAX(ApplicantID) to have an e-mail address of
tfs@.dlink.com or not.|||--Down with JOINS, up with table variables:
DECLARE @.MyApplicant TABLE
(
email varchar(50),
pw varchar(50),
fname varchar(50),
lname varchar(50),
AppID int
)
--grab records of interest
INSERT INTO @.MyApplicant (email, pw, fname, lname)
SELECT l.email, l.password, l.firstName, l.LastName
FROM logon l
WHERE l.email = 'tfs@.dlink.com'
--grab details pertaining to the records of interest by key information
UPDATE @.MyApplicant
SET AppID =
(
SELECT Max(a.ApplicantID)
FROM Applicant a
WHERE a.email = myapp.email
)
FROM @.MyApplicant myapp
SELECT *
FROM @.MyApplicant
--assuming indexes on email field in all tables, it doesn't get much
faster than this.
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:u7J2wekAFHA.2932@.TK2MSFTNGP10.phx.gbl...
> I had a question similar to this before and can't seem to make this one
> work.
> I have the following:
> Select
> ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
> from logon l join Applicant a on (l.email=a.email) where l.email =
> 'tfs@.dlink.com'
> This works fine if I don't have the max function, but it can give me
> multiple responses so I just want the last ApplicantID. This also works
if
> I put the password, email,firstName and LastName in a Groupby clause. Can
I
> change the max statement to prevent having to use the Groupby Clause.
> Thanks,
> Tom
>|||This may be unreasonable of me, but when you say max(applicantID), is this
really the last one? Could you not assign an applicantId that had already
been used? Do you not have a date when the assignment was made that you can
use to get the last assignment?
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23yQDFHmAFHA.3820@.TK2MSFTNGP11.phx.gbl...
> "Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
> news:41F567CA.CC06163B@.toomuchspamalready.nl...
> No, I was trying to use the following:
> Select
> ApplicantID=(max(ApplicantID)),l.password,l.email,l.firstName,l.LastName
> from logon l join Applicant a on (l.email=a.email) where l.email =
> 'tfs@.dlink.com'
> which does work fine if I take all fields out. I want the max
> ApplicantID, because it will be the last ApplicantID assigned for this
> email address.
> Tom
>|||"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message
news:uwWf3EpAFHA.2112@.TK2MSFTNGP09.phx.gbl...
> This may be unreasonable of me, but when you say max(applicantID), is this
> really the last one? Could you not assign an applicantId that had already
> been used? Do you not have a date when the assignment was made that you
> can use to get the last assignment?
In this particular case the applicantID is just a numeric ID that is
sequentially assigned. It happens to be an identity field. The max
applicantID will always be the latest one assigned.
Tom
> --
> ----
--
> Louis Davidson - drsql@.hotmail.com
> SQL Server MVP
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services. All other replies may be ignored :)
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:%23yQDFHmAFHA.3820@.TK2MSFTNGP11.phx.gbl...
>