CODEWITHSUNDEEP
sqlsql-server-2005
user278618
user278618

Reputation: 20222

Simple problem with MAX in sql

I have the table with rows:

ID          CountryCode Status
----------- ----------- -----------
2           PL          1
3           PL          2
4           EN          1
5           EN          1

and by the query

    SELECT [CountryCode]
      ,MAX([Status])
  FROM [TestTable]
  GROUP BY CountryCode,Status

I want to get:

CountryCode Status
----------- -----------
PL          2
EN          1

but I get:

CountryCode Status
----------- -----------
EN          1
PL          1
PL          2

What is wrong with this query?

Best regards

EDIT

Ok, Thanx for manz answers, but I didn't add part of query, which is :

Having Status != 3

so I think I must use Status in group by :/

Script to create and fill table:

USE [DatabaseName]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CountryCode] [nvarchar](2) NOT NULL,
    [Status] [int] NOT NULL
) ON [PRIMARY]

INSERT INTO dbo.TestTable
          ( CountryCode, Status )
  VALUES  ( 'PL', -- CountryCode - nvarchar(2)
            1  -- Status - int
            )

INSERT INTO dbo.TestTable
          ( CountryCode, Status )
  VALUES  ( 'PL', -- CountryCode - nvarchar(2)
            2  -- Status - int
            )

INSERT INTO dbo.TestTable
          ( CountryCode, Status )
  VALUES  ( 'EN', -- CountryCode - nvarchar(2)
            1  -- Status - int
            )
INSERT INTO dbo.TestTable
          ( CountryCode, Status )
  VALUES  ( 'EN', -- CountryCode - nvarchar(2)
            1  -- Status - int
            )

Upvotes: 0

Views: 65

Answers (4)

Steven Mastandrea
Steven Mastandrea

Reputation: 2772

You need to get rid of the group by status. The group by says return a new row for every unique combination of CountryCode and Status, which is not what you want.

You can add the where clause to exclude the rows that you don't want to consider in your query.

Try:

SELECT [CountryCode]
      ,MAX([Status])
  FROM [TestTable]
  WHERE status <> 3
  GROUP BY CountryCode

Upvotes: 5

James Moberg
James Moberg

Reputation: 4475

Don't group by the Status. Try this:

SELECT [CountryCode]
      ,MAX([Status])
  FROM [TestTable]
  GROUP BY CountryCode

Upvotes: 0

Alex Gitelman
Alex Gitelman

Reputation: 24722

When you add group by status it will do additional grouping within country code. Then your MAX selects maximum for country code. Remove status from group by.

Upvotes: 0

Frank Schmitt
Frank Schmitt

Reputation: 30765

Just drop status from the group by:

   SELECT [CountryCode]
      ,MAX([Status])
  FROM [TestTable]
  GROUP BY CountryCode

Upvotes: 2

Related Questions