Select the First Instance of a Record

Select the first instance of a record

You could use a CTE with ROW_NUMBER function:

WITH CTE AS(
    SELECT myTable.*
    , RN = ROW_NUMBER()OVER(PARTITION BY patientID ORDER BY ID)
    FROM myTable 
)
SELECT * FROM CTE
WHERE RN = 1

Select first occurrence of list item in table

This is a complete script to do the job

Declare @v_List Table
(
    Text nvarchar(100)
)

Declare @v_Data Table
(
    Number int,
    Text nvarchar(100)
)

Insert Into @v_List values(N'abc')
Insert Into @v_List values(N'efg')
Insert Into @v_List values(N'rty')

Insert Into @v_Data values(1, N'abcd')
Insert Into @v_Data values(2, N'efgh')
Insert Into @v_Data values(3, N'abcd')
Insert Into @v_Data values(4, N'rtyu')
Insert Into @v_Data values(5, N'efgh')

;with CTE as
(
    Select      D.Number,
                D.Text,
                ROW_NUMBER() OVER (PARTITION BY L.Text Order By D.Number) as Row_No
    From        @v_Data D
    Join        @v_List L
        On      D.Text like L.Text + '%'
)
Select  CTE.Number,
        CTE.Text
From    CTE
Where   CTE.Row_No = 1

SQL Server : select only first instance of record with multiple columns

I think the first step is to write a couple CTEs that get the data into a structure where you can evaluate player points regardless of win/loss. Here's a possible start:

;with PlayersPoints as 
(
   select m.MatchId
        ,m.SessionId 
        ,m.WeekNum
        ,ms.SetId       
        ,ms.WinningPlayer as PlayerId
        ,ms.WinningPoints as Points
        ,'W' as Outcome
        ,ms.MatchEndTime 
    from MatchSet ms
    join Match m on on ms.MatchId = m.MatchId
        and m.SessionId = @SessionId 

   union all 

   select m.MatchId
        ,m.SessionId 
        ,m.WeekNum
        ,ms.SetId   
        ,ms.LosingPlayer as PlayerId
        ,ms.LosingPoints as Points
        ,'L' as Outcome
        ,ms.MatchEndTime 
    from MatchSet ms
   join Match m on on ms.MatchId = m.MatchId
        and m.SessionId = @SessionId 
)
, PlayerMatch as 
(
   select SetId
        ,WeekNum
        ,MatchId
        ,PlayerId
        ,row_number() over (partition by PlayerId, WeekNum order by MatchEndTime) as PlayerMatchSequence
    from PlayerPoints 
)
....

The first CTE pulls out the points for each player, and the second CTE identifies which match it is. So for calculating individual points, you'd look for PlayerMatchSequence = 1.

Query to select row with first occurrence of an element

How can I have it so that it only shows the first record of each State?

Use window functions:

SELECT sm.*
FROM (SELECT State, Month,
             SUM(Sum_Confirmed) AS Max_Sum_Confirmed,
             ROW_NUMBER() OVER (PARTITION BY State ORDER BY SUM(SUM_Confirmed) DESC) as seqnum
      FROM `covid_by_countynmonth` 
      GROUP BY State, Month 
     ) sm
WHERE seqnum = 1;
ORDER BY State, Max_Sum_Confirmed DESC ;

CountyName seems superfluous so I removed it.

SQL Server SELECT first occurrence OR if no occurrence SELECT other criteria

The following approach uses ROW_NUMBER to retrieve a number based on your ordering logic within each CUSTOMER_ID group, then filters by the first record retrieved.

You may try the following:

SELECT 
    * 
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY CUSTOMER_ID
            ORDER BY (CASE WHEN EMAIL IS NOT NULL THEN 0 ELSE 1 END),CONTACT_VC 
        ) as rn
    FROM
        CUSTOMER_CONTACT
) t
WHERE rn=1

If you would like to join this to the customer table you may use the above query as a subquery eg

SELECT
   c.*,
   contact.*
FROM
   CUSTOMER c 
INNER JOIN (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY CUSTOMER_ID
            ORDER BY (CASE WHEN EMAIL IS NOT NULL THEN 0 ELSE 1 END),CONTACT_VC 
        ) as rn
    FROM
        CUSTOMER_CONTACT
) contact ON c.ID = contact.CUSTOMER_ID and contact.rn=1

Select the first occurrence of records with today's date using time field

Use option with EXISTS operator. Also, you can avoid not an efficient condition(or predicate) using the CAST function instead of CONVERT into VARCHAR data type

SELECT p.ProductName, p.Description, p.Quantity
FROM Products p INNER JOIN Transactions t ON p.ProductID = t.ProductID
WHERE t.ProductID = 'A6612' 
  AND CAST(t.Date_Tracked AS date) = CAST(GETDATE() AS date)
  AND EXISTS (                      
              SELECT 1
              FROM Transactions t2
              WHERE t.ProductID = t2.ProductID
                AND CAST(t2.Date_Tracked AS date) = CAST(GETDATE() AS date)
              HAVING MIN(t2.Time_Tracked) = t.Time_Tracked                      
              ) 
  AND Country = 'United States'

If the request return one record, simply use an ORDER BY clause with the TOP clause.

SELECT TOP 1 p.ProductName, p.Description, p.Quantity
FROM Products p INNER JOIN Transactions t ON p.ProductID = t.ProductID
WHERE t.ProductID = 'A6612' 
  AND CAST(t.Date_Tracked AS date) = CAST(GETDATE() AS date)
  AND p.Country = 'United States'
ORDER BY t.Time_Tracked

And finally if Date_Tracked has type datetime, then:

SELECT TOP 1 p.ProductName, p.Description, p.Quantity
FROM Products p INNER JOIN Transactions t ON p.ProductID = t.ProductID
WHERE t.ProductID = 'A6612' 
  AND CAST(t.Date_Tracked AS date) = CAST(GETDATE() AS date)
  AND p.Country = 'United States'
ORDER BY CAST(t.Date_Tracked AS time)

SQL: Return only first occurrence

If your seenTime increases as seenID increases:

select personID, min(seenTime) as seenTime
from personAttendances
group by personID

Update for another case:

If this is not the case, and you really want the seenTime that corresponds with the minimum seenID (assuming seenID is unique):

select a.personID, a.seenTime
from personAttendances as a
    join (
        -- Get the min seenID for each personID
        select personID, min(seenID) as seenID
        from personAttendances
        group by personID
    ) as b on a.personID = b.personID
where a.seenID = b.seenID

---

Related Topics