SQL - Ugly combination of GROUP BY and COALESCE

Question

I have a table with data similar to the following:

[ID], [State], [foo], [DateCreated], [DateUpdated]

The longer I work on this, the uglier my SQL is getting, which tells me I'm probably doing something wrong. What I want is a unique list of each State so long as foo is always the same for that State (if foo is not the same for all records in that State, I don't want that State at all). Also, I want to COALESCE DateCreated and DateUpdated and want the maximum value for that State.

So given this data:

[ID], [State], [foo], [DateCreated], [DateUpdated]
1,  MA, data1,  05/29/2012, 06/02/2012
2,  MA, data1,  05/29/2012, 06/03/2012
3,  RI, data2,  05/29/2012, NULL
4,  RI, data3,  05/29/2012, NULL
5,  NH, data4,  05/29/2012, NULL
6,  NH, data4,  05/29/2012, 06/05/2012

I'd like only these results:

[State], [foo], [LastUpdated]
MA, data1,  06/03/2012
NH, data4,  06/05/2012

What's the most elegant way to get what I'm after?

Foxglove

are you allowed to use views? – Arlinearlington

I can create views if that would help. – Foxglove

"The longer I work on this, the uglier my SQL is getting, which tells me I'm probably doing something wrong." Why do you feel this way? MySQL is the same language that makes you do a cartesian product just to find uniqueness, after all... – Urba

Answer 1

A simple Group by with nested queries should suffice:

Select State, coalesce(max_created,max_updated) from (
  Select State, min(foo) as min_foo, max(foo) as max_foo, 
    max(DateCreated) as max_created,
    max(DateUpdated) as max_updated
  From Data
  Group by State)
 Where min_foo = max_foo

Bestrew

The only significant change I made was to switch the order of the COALESCE so it favors the UPDATED date. Thanks again! – Foxglove

For anybody with a similar question, it's worth noting that MAX() and MIN() functions ignore NULLs. So MIN(foo) needs to be MIN(COALESCE(foo, '')) or something similar. – Foxglove

Answer 2

Another one:

http://sqlfiddle.com/#!6/fd219/1

SELECT
  t.State,
  MAX(t.foo),
  MAX( COALESCE( t.DateUpdated, t.DateCreated ))
FROM t
GROUP BY t.State
HAVING COUNT(DISTINCT t.foo) = 1;

Vasta

Oh i like this better than my answer... Nice one – Romberg

@Vasta - Just curious, but what do you mean SQL Fiddle didn't recognize US date formats? In what way were you having trouble? – Amadoamador

SQLFiddle is a great site. I haven't used it before. What a great idea when you don't have the ability to create your own database. – Foxglove

@JakeFeasel, how the hell do you find me when I say anything which could be possibly interpreted negatively? I'm scared of you :) BTW, OP's original sample data had two date formats per row: 2012-06-02 06/02/2012, probably this confused the Text2DDL engine. – Vasta

I probably would have used this one if I didn't have to check for NULLs. – Foxglove

I can't see where does my query fails, but whatever :) – Vasta

@Vasta ah, yeah the date parsing logic is a bit wonky - I'm deferring to the browser's date parsing engine, and they seem to be wild about what they'll do when passed a given string. Kind of annoying. As far as how I find you - just like to keep tabs on how people use the site! Don't worry - I don't just seek out possible criticism - I upvote too LOL! – Amadoamador

Answer 3

Not as elegant, but for you poor SQL 2000 souls:

SELECT T1.State, T2.Foo, T1.LastUpdated
FROM (
    SELECT State, MAX([ID]) AS [ID], 
        MAX(COALESCE(DateUpdated, DateCreated)) AS LastUpdated
    FROM YourTable
    GROUP BY State
    HAVING COUNT(DISTINCT Foo) = 1
) T1 
INNER JOIN YourTable T2 ON T1.State = T2.State AND T1.[ID] = T2.[ID]

Troche

Thank you for thinking of us unfortunate souls :) I love your user name by the way. I use that comic as an example of SQL injection all the time. – Foxglove

Why are the nested SELECTs are necessary? – Vasta

@Biziclop - they're actually not, it's just what I came up with at the time. Your edit is basically my inner SELECT with a max(Foo), which is what I was shooting for. – Troche

I had problems when foo is NULL. ALos I have foo1, foo2, etc and the NULLs really became a problem :( Frank's answer worker better in that situation. – Foxglove

@ScottK - You didn't mention Foo being NULL, but no worries, I should have taken that into account – Troche

Answer 4

Assuming you are using SQL Server 2005 or >

Try this:

WITH Data AS
(
    SELECT  *,
        COALESCE([DateCreated], [DateUpdated]) AS LastUpdated,
        ROW_NUMBER() OVER(PARTITION BY State ORDER BY COALESCE([DateCreated], [DateUpdated]) DESC) Position
      FROM <YOUR-TABLE> a
     WHERE NOT EXISTS
     (
        SELECT  1 
            FROM    <YOUR-TABLE> b
         WHERE  a.State = b.State
            AND a.foo <> b.foo
     )
)
SELECT State, foo, LastUpdated
  FROM Data
 WHERE Positon = 1

Internationale

I did forget to mention SQL version, sorry. I'm on SQL 2000. – Foxglove

Answer 5

Try this:

select state_name,foo,max(dateUpdated) from state where state_name in (select state_name from state group by state_name having count(distinct foo)=1) group by state_name,foo;

Clea