Insert Top 1 for Each Group

Transact-SQL
1

Hi experts. I have this SQL block:
Insert into dbo.Tables1
([ServerInstance]
,[DatabaseName]
,[Schema]
,[TableName]
,[ColName]
,[ColumnName]
,[ColumnValue]
,[DateCollected])

SELECT TOP 1
@@SERVERNAME,DB_Name(), null,null,null, ColumnName, ColumnValue, getDate()  FROM #Results
ORDER BY  ColumnName

It works but it only inserts 1 row. I have many different values in ColumnName. And ColumnName contains duplicate values.
**How can I select only 1 row for each ColumnName?

Thanks for any tips.

2

Maybe something like this?

SELECT TOP 1 WITH TIES
       @@SERVERNAME
     , DB_Name()
     , null
     , null
     , null
     , ColumnName
     , ColumnValue
     , getDate()
  FROM #Results
 ORDER BY
       ROW_NUMBER() OVER(PARTITION BY @@servername, db_name() ORDER BY ColumnName);
1 Like
3

Jeff, that's close to what I need. But it returns only 1 row per resultset. The #Results table contains many tables and column names (and there are duplicates by design)
For Example: ColumnName can contain
dbo.Table1.Column1
dbo.Table1.Column1
dbo.Table1.Column2

I need to select 2 rows in the example above:
dbo.Table1.Column1 and
dbo.Table1.Column2

Can this be done? And thanks very much.

4

I got it:

ROW_NUMBER() OVER(PARTITION BY @@servername, db_name(), ColumnName ORDER BY ColumnName);

Thanks for pointing me in the right direction!

5

Without seeing actual data - and sample results I am not sure if that will work. If partitioning by the column name works then great, but you have many other columns available that could be utilized - for example you could include the schema, table and ColName in the partition.

It also looks like ColumnName is actually a concatenation of those 3 columns...

6

You are correct, Jeff. It's sloppy. But next, I will break out that single string to populate the 3 columns. It's a 1 time project.

7 
select @@servername as servername,
DB_Name() as dbName,
t.name, 
c.name, 
getDate()
 from sys.tables t 
join sys.columns c on t.object_id = c.object_id 
 ORDER BY
       ROW_NUMBER() 
OVER(PARTITION BY @@servername, db_name() ORDER BY c.name);

maybe this might help as sample data

8

hi you can use

select distinct

OR

you can delete the duplicates from #Results
before inserting into table

9

hi

i tried to create sample data
-- thinking of all possible scenarios !!

please click arrow to the left for DROP Create SAMPLE Data 
drop table #Results

create table #Results 
(
 [ServerInstance] varchar(100) 
,[DatabaseName]   varchar(100) 
,[Schema]         varchar(100)
,[TableName]      varchar(100)
,[ColName]        varchar(100)
,[ColumnName]     varchar(100) 
,[ColumnValue]    varchar(100) 
,[DateCollected]  date 
)
go 

insert into #Results select 'Server234','DBSand','Schema09','TableOK','ColState','Column1','ColValue2222','2019-10-09'
insert into #Results select 'Server234','DBSand','Schema09','TableOK','ColState','Column1','ColValue2222','2019-10-09'

insert into #Results select 'Server333','DBSand','Schema09','TableOK','ColState','Column2','ColValue2222','2019-10-09'
insert into #Results select 'Server333','DBSand','Schema09','TableOK','ColState','Column2','ColValue2222','2019-10-10'
insert into #Results select 'Server333','DBSand','Schema09','TableOK','ColState','Column2','ColValue2222','2019-10-11'

insert into #Results select 'Server4','DBStuff','Schema11','TableOK','ColPick','Column2','ColValue2222','2019-10-09'
insert into #Results select 'Server4','DBStuff','Schema11','TableL ','ColSlab','Column3','ColValue2222','2019-10-10'


select * from #Results
go

image
image1682×391 256 KB