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?

"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... — Monica Heddneck, Apr 03 '17 at 22:29

biziclop · Answer 1 · 2012-07-10T18:13:39.067

12

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;

edited Jul 10 '12 at 18:13

answered Jul 10 '12 at 17:52

biziclop

14,466
3
49
65

1

Oh i like this better than my answer... Nice one – BLSully Jul 10 '12 at 17:53
@biziclop - Just curious, but what do you mean SQL Fiddle didn't recognize US date formats? In what way were you having trouble? – Jake Feasel Jul 10 '12 at 19:01
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. – Scott K Jul 10 '12 at 19:23
@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. – biziclop Jul 10 '12 at 19:38
I probably would have used this one if I didn't have to check for NULLs. – Scott K Jul 10 '12 at 19:45
I can't see where does my query fails, but whatever :) – biziclop Jul 10 '12 at 19:49
1

@biziclop 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! – Jake Feasel Jul 10 '12 at 20:15

score 5 · Accepted Answer · answered Jul 10 '12 at 17:57

5

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

answered Jul 10 '12 at 17:57

Frank Schmitt

30,195
12
73
107

The only significant change I made was to switch the order of the COALESCE so it favors the UPDATED date. Thanks again! – Scott K Jul 10 '12 at 18:54
1

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. – Scott K Jul 11 '12 at 22:08

score 3 · Answer 3 · answered Jul 10 '12 at 17:57

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]

answered Jul 10 '12 at 17:57

LittleBobbyTables - Au Revoir

32,008
25
109
114

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. – Scott K Jul 10 '12 at 18:06
Why are the nested `SELECT`s are necessary? – biziclop Jul 10 '12 at 18:17
@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. – LittleBobbyTables - Au Revoir Jul 10 '12 at 18:22
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. – Scott K Jul 10 '12 at 18:53
@ScottK - You didn't mention Foo being `NULL`, but no worries, I should have taken that into account – LittleBobbyTables - Au Revoir Jul 10 '12 at 18:57

score 1 · Answer 4 · answered Jul 10 '12 at 17:48

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

I did forget to mention SQL version, sorry. I'm on SQL 2000. — Scott K, Jul 10 '12 at 18:05

score 0 · Answer 5 · answered Mar 08 '18 at 16:28

0

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;

answered Mar 08 '18 at 16:28

Pooja smithi

53
5

SQL - Ugly combination of GROUP BY and COALESCE

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

5 Answers5

Linked