returning unique data

I have a table with 3 columns: number, name, rowid (and identity column). I
want to return the name of the first row (first meaning lowest rowid) when
there are more than one row with the same number. Given the table looks
like this:
number name rowid
102 bob 1
102 allen 2
104 carl 3
104 mike 4
I want only bob and carl to be returned.
Can anyone point me in the right direction?
Tx for any help.
Bernie YaegerYou're looking for a correlated subquery.
CREATE TABLE #foo
(
rowid INT IDENTITY(1,1),
[name] VARCHAR(12),
[number] INT
)
SET NOCOUNT ON
INSERT #foo([name], [number]) SELECT 'bob', 102
INSERT #foo([name], [number]) SELECT 'allen', 102
INSERT #foo([name], [number]) SELECT 'carl', 104
INSERT #foo([name], [number]) SELECT 'mike', 104
SELECT [name],[number],rowid
FROM #foo f1
WHERE rowid = (SELECT MIN(rowid)
FROM #foo f2
WHERE f2.[number] = f1.[number])
ORDER BY [number]
DROP TABLE #foo
"Bernie Yaeger" <berniey@.optonline.net> wrote in message
news:eN$erwlkFHA.1384@.TK2MSFTNGP10.phx.gbl...
>I have a table with 3 columns: number, name, rowid (and identity column).
>I want to return the name of the first row (first meaning lowest rowid)
>when there are more than one row with the same number. Given the table
>looks like this:
> number name rowid
> 102 bob 1
> 102 allen 2
> 104 carl 3
> 104 mike 4
> I want only bob and carl to be returned.
> Can anyone point me in the right direction?
> Tx for any help.
> Bernie Yaeger
>|||Bernie
CREATE TABLE #Test
(
[ID] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
number INT NOT NULL,
[name]CHAR(1)NOT NULL,
rowid INT NOT NULL
)
INSERT INTO #Test(number,[name],rowid) VALUES (102,'A',2)
INSERT INTO #Test (number,[name],rowid)VALUES (102,'B',1)
INSERT INTO #Test(number,[name],rowid) VALUES (103,'C',1)
INSERT INTO #Test(number,[name],rowid) VALUES (103,'D',2)
INSERT INTO #Test(number,[name],rowid) VALUES (103,'A',3)
INSERT INTO #Test(number,[name],rowid) VALUES (103,'B',4)
INSERT INTO #Test(number,[name],rowid) VALUES (102,'T',0)
INSERT INTO #Test(number,[name],rowid) VALUES (104,'a',2)
SELECT [name]FROM #Test
WHERE rowid=(SELECT MIN(rowid) FROM #Test T WHERE T.number=#Test.number)
"Bernie Yaeger" <berniey@.optonline.net> wrote in message
news:eN$erwlkFHA.1384@.TK2MSFTNGP10.phx.gbl...
>I have a table with 3 columns: number, name, rowid (and identity column).
>I want to return the name of the first row (first meaning lowest rowid)
>when there are more than one row with the same number. Given the table
>looks like this:
> number name rowid
> 102 bob 1
> 102 allen 2
> 104 carl 3
> 104 mike 4
> I want only bob and carl to be returned.
> Can anyone point me in the right direction?
> Tx for any help.
> Bernie Yaeger
>|||Hi Uri,
Tx for your help Uri.
Bernie
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eLVoxKmkFHA.3568@.TK2MSFTNGP10.phx.gbl...
> Bernie
> CREATE TABLE #Test
> (
> [ID] INT NOT NULL IDENTITY(1,1) PRIMARY KEY,
> number INT NOT NULL,
> [name]CHAR(1)NOT NULL,
> rowid INT NOT NULL
> )
> INSERT INTO #Test(number,[name],rowid) VALUES (102,'A',2)
> INSERT INTO #Test (number,[name],rowid)VALUES (102,'B',1)
> INSERT INTO #Test(number,[name],rowid) VALUES (103,'C',1)
> INSERT INTO #Test(number,[name],rowid) VALUES (103,'D',2)
> INSERT INTO #Test(number,[name],rowid) VALUES (103,'A',3)
> INSERT INTO #Test(number,[name],rowid) VALUES (103,'B',4)
> INSERT INTO #Test(number,[name],rowid) VALUES (102,'T',0)
> INSERT INTO #Test(number,[name],rowid) VALUES (104,'a',2)
>
> SELECT [name]FROM #Test
> WHERE rowid=(SELECT MIN(rowid) FROM #Test T WHERE T.number=#Test.number)
>
>
> "Bernie Yaeger" <berniey@.optonline.net> wrote in message
> news:eN$erwlkFHA.1384@.TK2MSFTNGP10.phx.gbl...
>|||Hi Aaron,
Tx so much for your help.
Bernie
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ORACDEmkFHA.708@.TK2MSFTNGP10.phx.gbl...
> You're looking for a correlated subquery.
>
> CREATE TABLE #foo
> (
> rowid INT IDENTITY(1,1),
> [name] VARCHAR(12),
> [number] INT
> )
> SET NOCOUNT ON
> INSERT #foo([name], [number]) SELECT 'bob', 102
> INSERT #foo([name], [number]) SELECT 'allen', 102
> INSERT #foo([name], [number]) SELECT 'carl', 104
> INSERT #foo([name], [number]) SELECT 'mike', 104
> SELECT [name],[number],rowid
> FROM #foo f1
> WHERE rowid = (SELECT MIN(rowid)
> FROM #foo f2
> WHERE f2.[number] = f1.[number])
> ORDER BY [number]
> DROP TABLE #foo
>
>
> "Bernie Yaeger" <berniey@.optonline.net> wrote in message
> news:eN$erwlkFHA.1384@.TK2MSFTNGP10.phx.gbl...
>