CODEWITHSUNDEEP
sqlsql-servert-sql
Navid_pdp11
Navid_pdp11

Reputation: 4012

Select first occurrence of list item in table

I have a list like this example:

abc, efg, rty

and a table with following data:

1 abcd
2 efgh
3 abcd
4 rtyu
5 efgh

now I want to find the first-row which start with list item in the table. my expected result is: 

1 abcd
2 efgh
4 rtyu

Upvotes: 1

Views: 3025

Answers (6)

DarkRob
DarkRob

Reputation: 3833

You may use this, there are so many ways to achieve this, use whichever suits you better.

using subquery

select id, col from 
        (select Row_number() over (partition by col order by id) as slno, id, col from yourtable) 
    as tb where tb.slno=1

using cte

; with cte as (
select row_number() over (partition by col order by id) as Slno, id, col from table)
select id, col from cte where slno=1

using min

select Min(id) , col from table group by col

Note:-

In the end of any above mentioned query you may apply your where clause to filter your records as needed.

Upvotes: 0

Slava Utesinov
Slava Utesinov

Reputation: 13488

select * from TableName
where Id in
(
    select min(Id) from
    (
        select Id, 
        case 
        when Val like 'abc%' then 1
        when Val like 'efg%' then 2
        when Val like 'rty%' then 3
        else 0 end temp
        from TableName
    )t where temp > 0
    group by temp
)

Upvotes: 1

B.Muthamizhselvi
B.Muthamizhselvi

Reputation: 642

SELECT * INTO #temp FROM (VALUES 
(1 ,'abcd'),
(2 ,'efgh'),
(3 ,'abcd'),
(4 ,'rtyu'),
(5 ,'efgh'))a([id], [name])

You can use min and group by function

SELECT MIN(id), name FROM #temp GROUP BY name

Upvotes: 0

Kim Hoang
Kim Hoang

Reputation: 1368

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

Upvotes: 3

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521379

For SQL Server I prefer this version, which does not require a subquery:

SELECT TOP 1 WITH TIES ID, Value
FROM yourTable
WHERE Value LIKE 'abc%' OR Value LIKE 'efg%' OR Value LIKE 'rty%'
ORDER BY ROW_NUMBER() OVER (PARTITION BY Value ORDER BY ID);

Upvotes: 0

EzLo
EzLo

Reputation: 14189

You can use a windowed ROW_NUMBER to generate a sequential number by each different value, then just display the first one only.

;WITH RowNumbersByValue AS
(
    SELECT
        T.ID,
        T.Value,
        RowNumber = ROW_NUMBER() OVER (PARTITION BY T.Value ORDER BY T.ID)
    FROM
        YourTable AS T
)
SELECT
    R.ID,
    R.Value
FROM
    RowNumbersByValue AS R
WHERE
    R.Value IN ('abcd', 'efgh', 'rtyu') AND
    R.RowNumber = 1

Upvotes: 0

Related Questions