(SQL) Select from database based on multiple pairs of pairs
The problem I am facing is trying to select rows from the database where the 2 columns in that row correspond to specific data pairs. In other words, selecting rows from data, where id = 1 AND type = 'news'
. Obviously if it was 1 simple pair it would be easy, but the problem is that we are fetching rows based on hundreds of data pairs. I feel like there must be some way to make this request without looping through the pairs and requesting them separately. I hope some SQL stackers can provide guidance.
Here's a breakdown of the complete code:
Suppose I have the following dataset where history_id
is the primary key. (I've simplified the structure a bit with respect to dates for readability.)
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'll 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 this subset of data, I need to determine how many of these records represent the first record in the database for each type / id. In other words, this line 4 for the first time in the database that appears id: 3
, type: news
? So I am using query with()
min()
.
In real code, two lists are programmatically generated from the result sets of our previous query. (I've summarized them here for readability.)
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 dataset:
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 will notice this entire original dataset because we match the ID and enter it individually in the lists, not as collective pairs.
As a result I want, but I cannot understand 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 walk the route of each pair and query the database to determine its first result, but that seems like an inefficient solution. I figured one of the SQL gurus on this site might be spreading some wisdom.
If I approach this situation the wrong way, the gist of the whole procedure is that the database stores all creations and changes in one table. I need to track the behavior of each user and determine how many records in the history table are being edited or created over a specific date range. So I select all pairs type:id
from the date range based on user_id
, and then for each pairing, I determine if the user is responsible for the first record that happens in the database. If first, then the "creation" is still "editing".
a source to share
SELECT *
FROM HISTORY,
(SELECT MIN(date) 'min_date', id, type
FROM history
WHERE id IN (1,2,3,4) AND type IN ('news','photo')
-- AND DATE BETWEEN xxx and YYY
GROUP BY id, type) 'min_dates'
WHERE HISTORY.id = min_dates.id
AND HISTORY.type = min_dates.type
AND HISTORY.date = min_dates.min_date
This is untested as I don't have access to the database at the moment, sorry
a source to share
SELECT
h1.history_id,
h1.id,
h1.type,
h1.user_id,
h1.date
FROM
( select
h2.id,
MIN( h2.history_id ) minHistory
from
history h2
group by
h2.id ) ByType,
history h1
where
ByType.MinHistory = h1.History_ID
This will query the entire system as a whole, regardless of dates. However, you can use WHERE criteria for an internal h2-history query to restrict the date range or the types of identifiers or type declarations.
Since the inner query will be executed first and will obviously have fewer records to be used as the main one to join the FULL history table. But since it is only based on a single history record, only that critical record will be the "first" returned, as you hope to receive.
a source to share