I'm using the Stack Exchange Data Explorer to learn SQL, but I think the fundamentals of the question is applicable to other databases.

I'm trying to query the Badges table, which according to Stexdex (that's what I'm going to call it from now on) has the following schema:

• Badges
  • Id
  • UserId
  • Name
  • Date

This works well for badges like [Epic] and [Legendary] which have unique names, but the silver and gold tag-specific badges seems to be mixed in together by having the same exact name.

Here's an example query I wrote for [mysql] tag:

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

The (slightly annotated) output is: as seen 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 

This is consistent with the current list of silver and gold earners at the moment of this writing, but to speak in more timeless terms, as of the end of May 2010 only 2 users have earned the gold [mysql] tag: Quassnoi and Bill Karwin, as evidenced in the above result by their names being the only ones that appear twice.

So this is the way I understand it:

• The first time an Id appears (in chronological order) is for the silver badge
• The second time is for the gold

Now, the above result mixes the silver and gold entries together. My questions are:

• Is this a typical design, or are there much friendlier schema/normalization/whatever you call it?
• In the current design, how would you query the silver and gold badges separately?
  • GROUP BY Id and picking the min/max or first/second by the Date somehow?
  • How can you write a query that lists all the silver badges first then all the gold badges next?
    • Imagine also that the "real" query may be more complicated, i.e. not just listing by date.
    • How would you write it so that it doesn't have too many repetition between the silver and gold subqueries?
  • Is it perhaps more typical to do two totally separate queries instead?
  • What is this idiom called? A row "partitioning" query to put them into "buckets" or something?