Can Individual Fields Be Suppressed In a Query? 1

[Sheffield]

Perhaps I'm not phrasing that correctly.

Below is a partial result of a query I'm working with:

ID CITY STATION TV HouseHolds
-- ------------ -------- -------------
1 New York WPXN 6,500,000
1 New York WWPS 38,000
4 Philadelphia WBPH 800,000
4 Philadelphia WPPX 2,238,000

Essentially, I'm being asked to 'suppress' (not display a value) the lower values for both New York and Philadelphia, while still displaying the ID, CITY & STATION, so the result will appear as such:

ID CITY STATION TV HouseHolds
-- ------------ -------- -------------
1 New York WPXN 6,500,000
1 New York WWPS
4 Philadelphia WBPH
4 Philadelphia WPPX 2,238,000

Is this even possible? I'm a SQL Server newbie so I'm not aware (yet) of any creative methods of achieving this result.

All help is greatly appreciated

 

[tlbroadbent]

You could use a CASE statment.

Example:

Select
ID, CITY, STATION,
Case When [TV HouseHolds]>=1000000
Then [TV HouseHolds] Else Null End AS TVHouseholds
From Table Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Sheffield]

Thanks for your response.

However, is there a way to achieve the same result without hardcoding a figure in the CASE stmt?

My resultset contains two (or three) Stations for 200+ Cities. I need to include every row, but can't have TV HouseHold overlap and need to (somehow) suppress the lower amounts for each city.

Thus, hardcoding 1,000,000 with the following example data.....

ID CITY STATION TV HouseHolds
-- ------------ -------- -------------
4 Philadelphia WBPH 800,000
4 Philadelphia WPPX 2,238,000
4 Philadelphia WCPX 1,050,000
6 Boston WBPX 18,000
6 Boston WDPX 13,000

...would provide the result below. (This would be bad because Boston wouldn't be counted and Philly would be overcounted.

ID CITY STATION TV HouseHolds
-- ------------ -------- -------------
4 Philadelphia WBPH
4 Philadelphia WPPX 2,238,000
4 Philadelphia WCPX 1,050,000
6 Boston WBPX
6 Boston WDPX

The correct result for this would be:

ID CITY STATION TV HouseHolds
-- ------------ -------- -------------
4 Philadelphia WBPH
4 Philadelphia WPPX 2,238,000
4 Philadelphia WCPX
6 Boston WBPX 18,000
6 Boston WDPX

Sorry for the lengthy reply. Once again, I'm fairly confident this is possible, but don't know the specifics on how to do it.

 

[rac2]

SELECT ID, CITY, STATION, 
   Case
      When [TV HouseHolds] = 
        (SELECT MAX(TV HouseHolds) FROM Table b
          WHERE b.CITY = a.CITY))
         Then [TV HouseHolds] 
      Else Null
   End AS "High Households"
FROM Table a
ORDER BY CITY, STATION, "High Households" DESC

maybe.

 

[Sheffield]

Many, many thanks for all help thus far However, it doesn't quite fix my problem.

The above response is very close, but returns the following result:

  ID   CITY          STATION     TV HouseHolds
  --   ------------  --------    -------------

   1   New York      WPXN        6,500,000
   1   New York      WWPS        NULL 
   4   Philadelphia  WBPH        NULL
   4   Philadelphia  WPPX        NULL
   6   Boston        WBPX        NULL
   6   Boston        WDPX        NULL

Unfortunately, since it uses the MAX fuction, it converts all TV HouseHold fields other than the largest to a NULL. Whereas I need only the largest number per city listed and the smaller number to be a NULL.

I like the logic, but need to build upon just a little. Does anyone else have another idea?

 

[tlbroadbent]

The query suggested by RAC2 should work though it has a couple of syntax errors. Try the following. The where clause on the inner query is critical and it appears that may be missing in your test.

SELECT ID, CITY, STATION,
Case
When [TV HouseHolds] =
(SELECT MAX([TV HouseHolds]) FROM Table b
WHERE b.CITY = a.CITY)
Then [TV HouseHolds]
Else Null
End AS "High Households"
FROM Table a
ORDER BY CITY, STATION, "High Households" DESC Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[Sheffield]

Ok, perhaps I'm learning disabled and need it spelled out a little more. However, doesn't the code below in BLUE ALWAYS return a single number?

SELECT ID, CITY, STATION,
Case
When [TV HouseHolds] =
(SELECT MAX([TV HouseHolds]) FROM Table b
WHERE b.CITY = a.CITY)
Then [TV HouseHolds]
Else Null
End AS "High Households"
FROM Table a
ORDER BY CITY, STATION, "High Households" DESC

...very confused...

 

[Sheffield]

Forgive me....you were correct all along. After a little tweaking and tuning, this actually does work.

Many, many thanks for your help