SQL Initial Question: Querying Gold and Silver Tag Icons in Stack Exchange Data Explorer

Question

I use Stack Exchange Data Explorer to learn SQL, but I think the basics of the question apply to other databases.

I am trying to query a table Badgesthat, according to Stexdex (what I'm going to call it now), has the following schema:

• Badges
  • Id
  • Userid
  • Name
  • the date

This works well for badges like [Epic]and [Legendary]that have unique names, but badges labeled silver and gold seem to mix together with the exact same name.

Here is an example of the query I wrote for the tag [mysql]:

SELECT
  UserId as [User Link],
  Date
FROM
  Badges
Where
  Name = 'mysql'
Order By
  Date ASC

Output (slightly annotated): as shown on stexdex :

User Link       Date                    
--------------- -------------------     // all for silver except where noted
Bill Karwin     2009-02-20 11:00:25     
Quassnoi        2009-06-01 10:00:16     
Greg            2009-10-22 10:00:25     
Quassnoi        2009-10-31 10:00:24     // for gold
Bill Karwin     2009-11-23 11:00:30     // for gold
cletus          2010-01-01 11:00:23    
OMG Ponies      2010-01-03 11:00:48     
Pascal MARTIN   2010-02-17 11:00:29 
Mark Byers      2010-04-07 10:00:35     
Daniel Vassallo 2010-05-14 10:00:38 

, , 2010 2 [mysql]: Quassnoi Bill Karwin, , , .

, :

• Id ( )

. :

• //, ?
• , ?
  • GROUP BY Id - min/max first/second Date?
  • , , ?
    • , "" , .. .
    • , ?
  • , ?
  • ? "" , "" - ?

---

, :

User Link       Date                    
--------------- -------------------     
Bill Karwin     2009-02-20 11:00:25     // result of query for silver
Quassnoi        2009-06-01 10:00:16     // :
Greg            2009-10-22 10:00:25     // :
cletus          2010-01-01 11:00:23     // :
OMG Ponies      2010-01-03 11:00:48     // :
Pascal MARTIN   2010-02-17 11:00:29     // :
Mark Byers      2010-04-07 10:00:35     // :
Daniel Vassallo 2010-05-14 10:00:38     // :
------- maybe some sort of row separator here? can SQL do this? -------
Quassnoi        2009-10-31 10:00:24     // result of query for gold
Bill Karwin     2009-11-23 11:00:30     // :

, . , .

Answer 1

//, ?

, , . , , .

, ? GROUP BY Id min/max first/second Date -?

- ( AKA), , . HAVING COUNT(*) >= 1 . GROUP BY HAVING COUNT (*) = 2` - , userid ...

, , ?

- , ? ORDER BY t.userid, t.date; , , (IE: ROW_NUMBER(), RANK())...

, ?

. , , ...

? "" , "" - ?

, : , , ...

Answer 2

- .

, , , , :

, , , .

: " mysql". :

SELECT
  UserId as [User Link],
  min(Date) as [Silver Date],
  case when count(*) = 1 THEN NULL ELSE max(date) END
FROM
  Badges
Where
  Name = 'mysql'
group by
  UserId
Order By
  case when count(*) = 1 THEN NULL ELSE max(date) END DESC, min(Date)

:

SQL: . . setb "" , :

SELECT
  UserId as [User Link],
  min(Date) as [Date],
  0 as dummyorder
FROM
  Badges
Where
  Name = 'mysql'
group by
  UserId
union all
select
  UserId as [User Link],
  max(Date) as [Date],
  1 as dummyorder
FROM
  Badges
Where
  Name = 'mysql'
group by
  UserId
having
  count(*) = 2
Order By
  dummyorder, Date