Extract data with condition and grouping from MS Access DB Table

I have the following table in MS Access :

FID FNAME                 SID   FBOOL
14  A11111_1111111_1.png    5   false
15  A11111_1111111_15.png   5   true
16  A11111_1111111_16.png   5   false
17  A11111_1111111_17.png   5   false
18  A1123_2345_1.png        7   false
19  A1123_2345_19.png       7   false
20  A1123_2345_20.png       7   false
 3  A111_111_1.bmp          8   true
10  A111_111_4.bmp          8   true
21  A111_111_13.png         8   false
31  A1123_2345_1.png        9   false
32  A1123_2345_19.png       9   false
33  A1123_2345_20.png       9                 <=EMPTY Value
34  A1123_2345_1.png        10                <=EMPTY Value
35  A1123_2345_19.png       10                <=EMPTY Value
36  A1123_2345_20.png       10                <=EMPTY Value
44  A1123_2345_20.png       11  true          
45  A1123_2345_1.png        11  false       
46  A1123_2345_19.png       11                <=EMPTY Value
47  A1123_2345_20.png       11  true

I would like to extract using SQL the FID in the following way:

15  A11111_1111111_15.png   5   true
20  A1123_2345_20.png       7   false
10  A111_111_4.bmp          8   true
33  A1123_2345_20.png       9                 <=EMPTY Value
36  A1123_2345_20.png       10                <=EMPTY Value
47  A1123_2345_20.png       11  true

This will be a subquery, so I can’t use UNION. (I tried with UNION, but it gives: “this operation is not allowed in subqueries”)

The header of SirData is:

SID PNumber SNumber STitle  STime   SNote  //SID is the primary key

The header of SirFoto is:

FID FNAME  SID   FBOOL  //the SID is a foreign key, FID is primary key

My query looks like this:

SELECT SirData.SID, SirData.PNumber, SirData.SNumber, SirData.STitle, SirData.STime, SirData.SNote, FOTO.FID, FOTO.FNAME FROM SirData LEFT JOIN ( Select SID, FID, FNAME FROM SirFoto where FID IN  (  Select MAX(FID) from SirFoto group by SID  order by MAX(FBOOL) desc, MAX(FID) desc   //I tried like this, but is incorrect !)  )  FOTO ON SirData.SID = FOTO.SID where SirData.PNumber=  selectedprc     order by SirData.PNumber ASC, SirData.SNumber ASC

Answer

You seem to want the latest row, with a preference for “true”. This suggests:

select sf.*
from SirFoto as sf
where sf.fid = (select top (1) sf2.fid
                from SirFoto as sf2
                where sf2.sid = sf.sid and
                order by iif(sf2.fbool, 'true', 1, 2),
                         sf2.id desc
               );

Advertisement

Answer