using MAX in where clause? - 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

SQL Server 2000 Forums

SQL Server Development (2000)

using MAX in where clause?

Author

Topic

PeterG
Posting Yak Master

156 Posts

Posted - 2003-02-11 : 11:09:32

My table structure is:

MyTable
DateSubmitted datetime
CompName varchar(30)
Segment1 varchar(30)

I'd like to get the latest Segment1 for a particular company. Please help me write the query. Thanks.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-11 : 11:20:28

SELECT TOP 1 *
FROM MyTable
WHERE CompName = 'some value'
ORDER BY Segment1 DESC

Or something like that. Without knowing what Segment1 represents it's hard to tell if it should be cast or compared to another value to determine what 'latest' really means.

PeterG
Posting Yak Master

156 Posts

Posted - 2003-02-11 : 11:46:25

What about the dateSubmitted field? This is actually what I'm struggling with.

Records in the table would be like this:
datesubmitted compname segment
10/12/2002 Comp1 Seg1
11/23/2002 Comp2 Seg2
11/10/2002 Comp1 Seg3
9/9/2002 Comp2 ABC
11/12/2002 Comp1 DEF

I'd like to get 'DEF' for the 11/23/2002 record for Comp1.

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-11 : 11:51:28

SELECT TOP 1 *
FROM MyTable
WHERE CompName = 'Comp1'
ORDER BY DateSubmitted DESC

If DateSubmitted isn't a date datatype, cast it in the the order by.

PeterG
Posting Yak Master

156 Posts

Posted - 2003-02-11 : 13:26:29

Thanks tfountain.

Using the above data, how would I grab the latest records for comp1 and comp2? My recordset should have these:

11/23/2002 Comp2 Seg2
11/12/2002 Comp1 DEF

M.E.
Aged Yak Warrior

539 Posts

Posted - 2003-02-11 : 18:44:25

Done wrong.. will resubmit
-----------------------
SQL isn't just a hobby, It's an addiction

Edited by - M.E. on 02/11/2003 18:51:28

M.E.
Aged Yak Warrior

539 Posts

Posted - 2003-02-11 : 18:53:21

Okies, lemmee see if I got this right.

Select Compname,Max(datesubmitted)
from Mytable
group by compname

-----------------------
SQL isn't just a hobby, It's an addiction

PeterG
Posting Yak Master

156 Posts

Posted - 2003-02-11 : 19:11:38

Thanks M.E. It works if I don't want the segment field in my select list. If I include it in the list, I get this error:

"Column 'segment' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-02-11 : 19:39:04

Here you go:

SELECT Compname, Segment1
FROM
MyTable
INNER JOIN
(
SELECT CompName, Max(DateSubmitted) as MaxDate
FROM MyTable
GROUP BY CompName
)
A ON
MyTable.CompName = A.CompName AND
MyTable.DateSubmitted = MaxDate

- Jeff

tfountain
Constraint Violating Yak Guru

491 Posts

Posted - 2003-02-11 : 20:22:29

Or yet another variation ;):

SELECT <pick your columns>
FROM MyTable AS A
WHERE A.DateSubmitted =
(
SELECT MAX(B.DateSubmitted)
FROM MyTable AS B
WHERE B.CompName = A.CompName
)

PeterG
Posting Yak Master

156 Posts

Posted - 2003-02-12 : 11:06:58

Thanks tfountain and Jeff. You guys are awesome.

davidpardoe
Constraint Violating Yak Guru

324 Posts

Posted - 2003-03-03 : 08:00:21

Doesn't help you if there is a tie - ie you will get all those for the latest date (if the date doesn't go down to time as well.

If you only want one per company and there are ties you need something to break ties.

============================
Chairman of
The NULL Appreciation Society
"Keep NULLs as NULL"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources