0

I need some help to improve part of my query. The query is returning the correct data, I just need to exclude some extra information that I don't need.

I believe that one of the main parts that will change is:

JOIN TBL_DATA_TYPE_RO_BODY TB ON TB.FK_ID_TBL_FILE_NAMES=VMI.ID_TBL_FILE_NAMES

In this part, I have, for example, 2 FK_ID_TBL_FILE_NAMES, it will return 2 results from TBL_DATA_TYPE_RO_BODY.

The data that I have is (I excluded some extra columns):

  • If I have 2 or more equal MAG for the same field "ONLY_FIELD_NAME" I should return only the first one (I don't care about the others one). I believe that this is a simple case for Group by, but I am having trouble doing the group by on the join.

My ideas:

  1. Use select top (i.e. here)
  2. Use first valeu (i.e. here)

What I have (note the 2 last lines):

Freq|Mag|Phase|Date|ONLY_FILE_NAME
1608039|767|3234|37:00.0|RO_Mass_Load_4b
1608039|781|3371|44:00.0|RO_Mass_Load_4b
1608039|788|3138|37:00.0|RO_Mass_Load_4b
1608039|797|3326|44:00.0|RO_Mass_Load_4b
1608039|808|3117|37:00.0|RO_Mass_Load_4b
1608039|808|3269|44:00.0|RO_Mass_Load_4b

What I would like to have (note the last line):

Freq|Mag|Phase|Date|ONLY_FILE_NAME
1608039|767|3234|37:00.0|RO_Mass_Load_4b
1608039|781|3371|44:00.0|RO_Mass_Load_4b
1608039|788|3138|37:00.0|RO_Mass_Load_4b
1608039|797|3326|44:00.0|RO_Mass_Load_4b
1608039|808|3117|37:00.0|RO_Mass_Load_4b

Note that the mag field is coming from my JOIN.

Ideas? Any help?

In case you wanna see the whole code is:

SELECT  TW.CURRENT_MEASUREMENT as Cycle_Current_Measurement, 
TW.REF_MEASUREMENT as Cycle_Ref_Measurement, 
CONVERT(REAL,TT.CURRENT_TEMP) as Cycle_Current_Temp, 
CONVERT(REAL,TT.REF_TEMP) as Cycle_Ref_Temp, 
TP.TYPE as Cycle_Type, TB.FREQUENCY as Freq, 
TB.MAGNITUDE as Mag, 
TB.PHASE as Phase, 
VMI.TIME_FORMATTED as Date, 
VMI.ID_TBL_FILE_NAMES as IdFileNames, VMI.ID_TBL_DATA_TYPE_RO_HEADER as IdHeader, VMI.*  
FROM VW_MAIN_INFO VMI 
JOIN TBL_DATA_TYPE_RO_BODY TB ON TB.FK_ID_TBL_FILE_NAMES=VMI.ID_TBL_FILE_NAMES
LEFT JOIN TBL_POINTS_AND_CYCLES TP  ON VMI.ID_TBL_DATA_TYPE_RO_HEADER = TP.FK_ID_TBL_DATA_TYPE_RO_HEADER 
LEFT JOIN TBL_POINTS_AND_MEASUREMENT TW  ON VMI.ID_TBL_DATA_TYPE_RO_HEADER = TW.FK_ID_TBL_DATA_TYPE_RO_HEADER 
LEFT JOIN TBL_POINTS_AND_TEMP TT  ON VMI.ID_TBL_DATA_TYPE_RO_HEADER = TT.FK_ID_TBL_DATA_TYPE_RO_HEADER
FFLS
  • 565
  • 1
  • 4
  • 19

1 Answers1

0

Try something like this. the partition by is like a group by; it defines groups over which row_number will auto-increment an integer by 1. The order by tells row_number which rows should have a lower number. So in this example, the lowest date will have RID = 1. Then subquery it, and select only those rows which have RID = 1

select *
from (select RID = row_number() over (partition by tb.Magnitude order by vmi.time_formatted)
      from ...<rest of your query>) a
where a.RID = 1
Xedni
  • 3,662
  • 2
  • 16
  • 27