Sql Query to Return Only First Occurance of One Column Value

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

SQL query to return only first occurance of one column value

You can use the row_number function to get one id (ordered ascending) per type.

select id, type from 
(
select id, name, type, "group", 
row_number() over(partition by type order by type) as rn
from lists
) t
where rn = 1

Get the row with first occurrence of repetitive value in column using SQL Server

You can use the WITH TIES option in concert with the window functions lead() and row_number()

Example

Declare @YourTable Table ([Id] varchar(50),[Order] int,[Value] varchar(50))  Insert Into @YourTable Values 
 ('aa',0,'cat')
,('ba',1,'dog')
,('bb',2,'yuk')
,('dc',3,'gen')
,('ca',4,'cow')
,('c1',5,'owl')
,('b0',7,'ant')
,('h9',8,'fly')
,('t4',9,'bee')
,('g2',10,'fox')
,('ea',11,'rat')
,('fa',12,'pig')
,('gu',13,'pig')
,('co',14,'pig')
,('fo',15,'pig')
,('ou',16,'pig')
,('eo',17,'pig')
,('ii',18,'pig')
 
Select top 1 with ties *
 From @YourTable
 Order By case when lead(value,1) over (order by [order]) = value then 1 else 2 end
         ,row_number() over (order by [Order])

Results

Id  Order   Value
fa  12      pig

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

How to select only the first rows for each unique value of a column?

A very simple answer if you say you don't care which address is used.

SELECT
    CName, MIN(AddressLine)
FROM
    MyTable
GROUP BY
    CName

If you want the first according to, say, an "inserted" column then it's a different query

SELECT
    M.CName, M.AddressLine,
FROM
    (
    SELECT
        CName, MIN(Inserted) AS First
    FROM
        MyTable
    GROUP BY
        CName
    ) foo
    JOIN
    MyTable M ON foo.CName = M.CName AND foo.First = M.Inserted

Why Select * Only return the first record in a group?

But select only returns the first record in the group, not all records in the group. Why?

Because one row per group is what GROUP BY does.

The way to do what you want to do is to figure out which countries have more that 5 customers, then return all records from those countries:

SELECT * FROM Customers WHERE Country IN
(SELECT Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5)

---

Related Topics