Geting only the max value - 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

General SQL Server Forums

New to SQL Server Programming

Geting only the max value

Author

Topic

ionmang20
Starting Member

2 Posts

Posted - 2013-06-03 : 20:03:00

The folowing code :


SELECT DISTINCT Id1, A1.Id2
FROM    Table A1 
WHERE   1 <=
        (SELECT COUNT (*)
        FROM    Table A2
        WHERE   A1.Id2 = A2.Id2)

outputs :


                  ID1       ID2
-------------------- ----------
                   1          1
                   1          2
                   2          3
                   3          4
                   5          6
                   5          7

but I want to get the only one ID1 with the maximum value of ID2 like this :


                  ID1       ID2
-------------------- ----------
                   1          2
                   2          3
                   5          7

what do I do wrong ?

MuMu88
Aged Yak Warrior

549 Posts

Posted - 2013-06-03 : 21:34:55

Your WHERE clause will always be true because your table count is 6,
so 1 <= 6 is always true so you get all the rows from the table in your select statement.

What you may want to do is get the maximum value of ID2 using grouping of your data by ID1 as shown below in the first query.
OR
As in the second query below, use row numbers to number each rows of the table again by grouping by ID1 and ordering in descending order of ID2 value.

[CODE]
DECLARE @T1 TABLE(ID1 INT, ID2 INT)

INSERT INTO @T1 (ID1, ID2) VALUES
(1, 1),
(1, 2),
(2, 3),
(3, 4),
(5, 6),
(5, 7);

SELECT DISTINCT A1.Id1, A2.Id2
FROM @T1 A1 CROSS APPLY
(SELECT A2.ID1, MAX(ID2) as ID2
FROM @T1 A2
GROUP BY ID1) A2 WHERE A1.ID1 = A2.ID1;

-- OR

SELECT A2.Id1, A2.Id2
FROM (SELECT A2.ID1, ID2, ROW_NUMBER() OVER(PARTITION BY ID1 ORDER BY ID2 DESC) as RN
FROM @T1 A2) A2 WHERE RN = 1;

[/CODE]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 00:15:42

the closest to your original query which can give you correct result is this


SELECT DISTINCT Id1, A1.Id2
FROM    Table A1 
WHERE   1 <=  NOT EXISTS
        (SELECT  COUNT (*) 1
        FROM    Table A2
        WHERE   A1.Id1 = A2.Id1
        AND A2.ID2 > A1.ID2)

EDIT: You dont need DISTINCT as well
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

ionmang20
Starting Member

2 Posts

Posted - 2013-06-04 : 09:10:34

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-04 : 13:02:41

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

cursor system
Starting Member

2 Posts

Posted - 2013-06-04 : 14:04:17

it should be like his

select id1, a1.id2
from table a1
where id1=(select max(id2)
from table)

Get helpful tips on technology @ http://www.bestpricebestdeals.blogspot.com

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-05 : 00:04:56

quote:
Originally posted by cursor system

it should be like his

select id1, a1.id2
from table a1
where id1=(select max(id2)
from table)

Get helpful tips on technology @ http://www.bestpricebestdeals.blogspot.com

Nope...not quite

this will only return records from table having max id2 value whereas what op wanted was to get record with max id2 for each id1 value groups

so it should be


select id1, a1.id2
from table a1
where id2=(select max(id2)
           from table
           where id1 = a.id1)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources