CODEWITHSUNDEEP
sqlselectfirebirdgreatest-n-per-group
gabr
gabr

Reputation: 26850

Filtering result set to determine minimum values

I have three tables, connected as such

[PLAYLIST].DBI_ID =1:1= [SUB_FILES].SBF_PLY_ID =*:1= [STL].DBI_ID

From this database, I can fetch some data:

SELECT 
  PLAYLIST.dbi_id as PLY_ID,
  SUB_FILES.dbi_id as SBF_ID,
  SUB_FILES.dbi_created_time as SBF_CREATED,
  STL.dbi_id as STL_ID
FROM PLAYLIST, SUB_FILES, STL
WHERE (PLAYLIST.ply_automation_id = 'F0117705' 
      OR PLAYLIST.ply_automation_id = 'F0117707')
  AND (SUB_FILES.sbf_ply_id = PLAYLIST.dbi_id)
  AND (SUB_FILES.sbf_stl_id = STL.dbi_id)

returned data

The PLAYLIST.ply_automation_id = ... condition is used for testing only. At the end I'll be running the query over whole PLAYLIST table.

I can't figure out how to get only the record with the smallest SBF_CREATED date for each distinct STL_ID value.

In other words, I would only like to get back data for PLY_ID's 14.604 (which has the smallest SBF_CREATED of both records with STL_ID = 5.596) and 14.747 (which has the smallest SBF_CREATED of all four records with STL_ID = 5.642).

The DB in question is Firebird 2.1, but if possible I'm interested in a generic SQL answer.

Upvotes: 1

Views: 57

Answers (1)

jpw
jpw

Reputation: 44901

From your description I would assume that the result you actually want is:

PLY_ID  SBF_ID  SBF_CREATED             STL_ID
14.604  16044   2008-11-28 15:46:00.000 5.596
14.747  16.210  2008-12-11 11:30:00.000 5.642

As those are the ones with lowest SBF_CREATED.

What you can do is to find the lowest date for each sbf_stl_id in a derived table that you join with:

SELECT 
  PLAYLIST.dbi_id as PLY_ID,
  SUB_FILES.dbi_id as SBF_ID,
  SUB_FILES.dbi_created_time as SBF_CREATED,
  STL.dbi_id as STL_ID
FROM PLAYLIST
JOIN SUB_FILES ON SUB_FILES.sbf_ply_id = PLAYLIST.dbi_id
JOIN STL ON SUB_FILES.sbf_stl_id = STL.dbi_id
JOIN 
(
    SELECT sbf_stl_id, MIN(dbi_created_time) AS min_dbi_created_time 
    FROM SUB_FILES 
    GROUP BY sbf_stl_id
) AS sub ON sub.sbf_stl_id = stl.dbi_id 
        AND sub.min_dbi_created_time = SUB_FILES.dbi_created_time
WHERE (PLAYLIST.ply_automation_id = 'F0117705' OR PLAYLIST.ply_automation_id = 'F0117707')

This gives the desired result when I tested using SQL Server, but as it doesn't use any special product specific features but rather just standard SQL it should work with any standard compliant database.

Upvotes: 3

Related Questions