Max on a 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

SQL Server 2000 Forums

SQL Server Development (2000)

Max on a Query

Author

Topic

jijo_jacob
Starting Member

1 Post

Posted - 2002-01-23 : 14:15:21

I have a table, with 2 fields id and name

i likes to select Max(id) and the Corresponding name from the
table in a single query.

I tried in many ways with, having and group by and all nothing os worked out.

Is there is any way to select like that, or do we need to use seperate queries.

Thanks
Jijo

allyanne
Starting Member

18 Posts

Posted - 2002-01-23 : 14:34:13

How 'bout... (used Northwind db for example)

SELECT EmployeeID, LastName
FROM Employees
WHERE EmployeeID = (
SELECT MAX(EmployeeID)
FROM Employees
)

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-01-23 : 14:34:40

This should be a relatively straightforward operations so I wonder if you've left anything out. Anyway, here is a sample to pull the max(orderid) and corresponding customerid from the northwind sample database...


select orderid, customerid
from orders
where orderid = (SELECT Max(orders.OrderID) FROM orders)

hth,
Justin

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-01-23 : 14:35:00

Try this

Select a.*
from table1 as a
join (select max(id) as maxid from table1) as b
on a.id = b.maxid

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-01-23 : 14:35:58

Dang it! Sniped by allyanne, but it looks like I was still faster than Tigger. Hehehe

Justin

allyanne
Starting Member

18 Posts

Posted - 2002-01-23 : 14:40:07

yeah, but Tigger's example is better 'cause I assumed ID would be an identity...

Tigger
Yak Posting Veteran

85 Posts

Posted - 2002-01-23 : 14:41:43

Hey it's 8:30 in the morning here - I'm not awake yet !!

BTW - like the use of the select statement in the where clause - didn't know you could do that !

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-01-23 : 16:12:12

quote:

yeah, but Tigger's example is better 'cause I assumed ID would be an identity...

Tigger's example is identical to ours in how it determines the max. We all took it for granted that the id is sequential. But for the purposes of example it should be sufficient.

Justin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources