SELECT MAX - 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)

SELECT MAX

Author

Topic

burbakei
Yak Posting Veteran

80 Posts

Posted - 2003-06-14 : 09:04:10

i have a table with the following data

id qty
-- ---
1 53
2 65
3 26
1 79
2 36
3 41

i want to get the max of qty with its related id. i tried this

SELECT TOP 1 id, qty
FROM (SELECT id, MAX(qty) qty
FROM t1
GROUP BY id) a
ORDER BY qty DESC

there is no matter if two or more ids have the max in common. i just want to get one of them. i want to know is there a better way to do that?
thanks

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-06-14 : 10:15:36


select top 1
    id,
    qty
from
    t1 t
where
    not exists (
        select 1
        from
            t1
        where
            t.qty < tqy)

Jay White
{0}

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-06-14 : 10:38:10

SELECT TOP 1 id, qty
FROM t1
ORDER BY qty DESC

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-06-14 : 11:00:10

Yeah, that too

Jay White
{0}

SamC
White Water Yakist

3467 Posts

Posted - 2003-06-14 : 17:53:06

But he wants all the max qtys for all ids, but the qtys must be unique.

SELECT id, Max(qty) qty -- got the maxes, may be duplicate maxes
FROM t1
GROUP BY id

-- Then, prest-o change-o

SELECT qty, max(id) -- arbitrarily pick the max id for a given qty
FROM (
-- insert the query above here
) X
group by qty

Sam

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources