The problem i've encountered is attempting to select rows from a database where 2 columns in that row align to specific pairs of data. IE selecting rows from data where id = 1 AND type = 'news'. Obviously, if it was 1 simple pair it would be easy, but the issue is we are selecting rows based on 100s of pair of data. I feel as if there must be some way to do this query without looping through the pairs and querying each individually. I'm hoping some SQL stackers can provide guidance.

Here's a full code break down:

Lets imagine that I have the following dataset where history_id is the primary key. I simplified the structure a bit regarding the dates for ease of reading.

table: history
history_id  id  type  user_id  date
1           1   news  1        5/1
2           1   news  1        5/1
3           1   photo 1        5/2
4           3   news  1        5/3
5           4   news  1        5/3
6           1   news  1        5/4
7           2   photo 1        5/4
8           2   photo 1        5/5

If the user wants to select rows from the database based on a date range we would take a subset of that data.

SELECT history_id, id, type, user_id, date FROM history WHERE date BETWEEN '5/3' AND '5/5'

Which returns the following dataset

history_id  id  type  user_id  date
4           3   news  1        5/3
5           4   news  1        5/3
6           1   news  1        5/4
7           2   photo 1        5/4
8           2   photo 1        5/5

Now, using that subset of data I need to determine how many of those entries represent the first entry in the database for each type,id pairing. IE is row 4 the first time in the database that id: 3, type: news appears. So I use a with() min() query.

In real code the two lists are programmatically generated from the result sets of our previous query, here I spelled them out for ease of reading.

WITH previous AS (
  SELECT history_id, id, type FROM history WHERE id IN (1,2,3,4) AND type IN ('news','photo')
) SELECT min(history_id) as history_id, id, type FROM previous GROUP BY id, type

Which returns the following data set.

history_id  id  type  user_id  date
1           1   news  1        5/1
2           1   news  1        5/1
3           1   photo 1        5/2
4           3   news  1        5/3
5           4   news  1        5/3
6           1   news  1        5/4
7           2   photo 1        5/4
8           2   photo 1        5/5

You'll notice it's the entire original dataset, because we are matching id and type individually in lists, rather than as a collective pairs.

The result I desire is, but I can't figure out the SQL to get this result.

history_id  id  type  user_id  date
1           1   news  1        5/1
4           3   news  1        5/3
5           4   news  1        5/3
7           2   photo 1        5/4

Obviously, I could go the route of looping through each pair and querying the database to determine it's first result, but that seems an inefficient solution. I figured one of the SQL gurus on this site might be able to spread some wisdom.

In case I'm approaching this situation incorrectly, the gist of the whole routine is that the database stores all creations and edits in the same table. I need to track each users behavior and determine how many entries in the history table are edits or creations over a specific date range. Therefore I select all type:id pairs from the date range based on a user_id, and then for each pairing I determine if the user is responsible for the first that occurs in the database. If first, then creation else edit.

Any assistance would be awesome.