T-SQL to show first record in a grouping - how to do a First record (1 Viewer)

Rx_

Nothing In Moderation
Local time
Yesterday, 19:05
Joined
Oct 22, 2009
Messages
2,803
Trying to create a View that shows the Top 1 ID_Wells in a table where the Autocounter (record ID) is APD_FieldWorkDates.
The table name is APDFieldWorkDate_2

Here is an article with data example:
http://sqlserverplanet.com/tsql/selecting-top-n-records-per-group

I didn't do the first T-SQL statement because the two columns needed already exist.

My code looks like:
Code:
SELECT     ID_Wells, ID_Bio_Svy_Type, Dt_Arch_Req AS DtRequested, Dt_Survey AS HasPlats, Dt_Arch_Rpt AS DtReceived, APD_FieldWorkDates
FROM         APD_FieldWorkDate_2
WHERE     (ID_Bio_Svy_Type IN (15, 18)) AND (NOT (Dt_Survey IS NULL)) AND (NOT (Dt_Arch_Rpt IS NULL)) AND (ID_Wells IN
                          (SELECT   top 1   APD_FieldWorkDate_2.ID_Wells
                            FROM          APD_FieldWorkDate_2 
                            Where APD_FieldWorkDates = APD_FieldWorkDate_2.APD_FieldWorkDates
                            Order by APD_FieldWorkDate_2.APD_FieldWorkDates))
 order by ID_Wells

It works, but sadly, only returns 1 single record (a top 2 returns 2 records).
Trying to get the first Record for a Grouping of ID_Wells
also that meets the criteria shown.
Just baffled!

This View will eventually be outer joined by another query based on the ID_Wells.

I got this to work using Access SQL, using the Grouping
But, the sql won't translate into SQL Server.
 

Rx_

Nothing In Moderation
Local time
Yesterday, 19:05
Joined
Oct 22, 2009
Messages
2,803
This seems to work. Using First in an Access Group-By query failed in SQL Server. However, in T-SQL changing the First to Max - the query worked and returned the same total result of records.

Just could not get anything else to work due to the criteria required, short of writing a 2nd query to run against the first one.


Code:
SELECT APD_FieldWorkDate_2.ID_Wells, Max(APD_FieldWorkDate_2.APD_FieldWorkDates) AS FirstOfAPD_FieldWorkDates, APD_FieldWorkDate_2.ID_Bio_Svy_Type, APD_FieldWorkDate_2.Dt_Arch_Req AS DtRequested, APD_FieldWorkDate_2.Dt_Survey AS HasPlats, APD_FieldWorkDate_2.Dt_Arch_Rpt AS DtReceived
FROM APD_FieldWorkDate_2
GROUP BY APD_FieldWorkDate_2.ID_Wells, APD_FieldWorkDate_2.ID_Bio_Svy_Type, APD_FieldWorkDate_2.Dt_Arch_Req, APD_FieldWorkDate_2.Dt_Survey, APD_FieldWorkDate_2.Dt_Arch_Rpt
HAVING (((APD_FieldWorkDate_2.ID_Bio_Svy_Type) In (15,18)) AND (Not (APD_FieldWorkDate_2.Dt_Survey) Is Null) AND (Not (APD_FieldWorkDate_2.Dt_Arch_Rpt) Is Null))
ORDER BY APD_FieldWorkDate_2.ID_Wells;

Just to followup - this can be very tricky.
Two of my three worked. One of these ended up with hundreds of duplicates. So, I used the SELECT DISTINCT ID_Wells, MaxOfintID_APD_FedStCo
That knocked the results down to 12 duplicates out of over 10,000 records

I am going to have to look at the first method again.
It seems to be more complex when the filters are used.
 
Last edited:

Users who are viewing this thread

Top Bottom