?
Solved

How to get the latestdata from this table...

Posted on 2008-11-06
15
Medium Priority
?
402 Views
Last Modified: 2012-05-05
Hi,
I'm using SQL 2005 or 2008

Imagine a table with the following appearance:

ID    Col1    Col2    Col3
==   ====  ====  ====
1      123     234     NULL
2      23       NULL   456
3      NULL   21       NULL


Is there a way to get the latest values of each column on the table?
On this case I wnat to get:

Col1    Col2    Col3
====  ====  ====
23       21        456


Note that I want to make this query generic for every table, so I don't know the schema of the table :)
I can do this on the C# side but it would be nice to have it all on the SQL inside a SP.

Thanks,
Alex
0
Comment
Question by:Alexandre Simões
  • 5
  • 5
  • 2
  • +1
14 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22893966
what do you mean by generic? you don't know the column names in advance?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 22893987
try this

with a as (
 select row_number() over(order by case when col1 is null then -1 else id end) as row1,
           row_number() over(order by case when col2 is null then -1 else id end) as row2,
           row_number() over(order by case when col3 is null then -1 else id end) as row3,
  col1,col2,col3, count(id) over() as cnt
from your_table)
select  max(case when cnt=row1 then col1 else null end) as col1,
           max(case when cnt=row2 then col2 else null end) as col2,
           max(case when cnt=row3 then col3 else null end) as col3
from a
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22901824
Here ya go
select
 (select Col1 from ExampleTable where ID = 
	(select max(ID) from ExampleTable where Col1 IS NOT NULL)) Col1
,(select Col2 from ExampleTable where ID = 
	(select max(ID) from ExampleTable where Col2 IS NOT NULL)) Col2
,(select Col3 from ExampleTable where ID = 
	(select max(ID) from ExampleTable where Col3 IS NOT NULL)) Col3

Open in new window

results.bmp
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 30

Author Comment

by:Alexandre Simões
ID: 22903154
Hi, thanks for the replies.

momi_sabag: looks good and works fine, I tend not to like CTE's tho as they're slow.
HoggZilla: that's how I'm doing it right now. Works fine.

Now, both approaches work but what I really wanted from this question was the Generic part of it.
If possible I would like to make it dynamic to be used on several tables with different schemas rather that having to code on SP per table.

I know this will be slower but I don't mind if it would be a bit slower but generic.

Any ideas?
0
 
LVL 17

Expert Comment

by:HoggZilla
ID: 22903424
Will the first column in the table be an integeter that can be used to determine insert order, eg Identity column?
0
 
LVL 30

Author Comment

by:Alexandre Simões
ID: 22903470
We certainly have to make some rules.
One can be assuming that the first columns is the identity and its type is int, no problem.

thanks,
Alex
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22913171
CTE query would be faster than the inline multiple queries I would think - need to be a very large table to check, and probably no real difference in smaller tables... And a bit of a contrast with your other comment about flexibility versus speed...

certainly the inline query is "easier" to do some dynamic SQL on, and identifying the MAX(ID) as a link is not real hardship, the problem is how do you determine the column ? Are there any other rules ? Are you going to know the table_name ?

You can grab the table definition from the information_schema using columns ordinal position and run a series of selects using each column that way, but it is not going to be very fast... Two ways, either loop, or, use a concatenation technique using for xml...

Assumed that each table has a column known as ID and you have to nominate the table name...


--
-- method 1 loop through information schema building up some dynamic sql
--
declare @table_name varchar(200)
declare @column_name varchar(200)
declare @sql varchar(max)
declare @i int
 
set @table_name = 'customers'
set @sql = ''
set @i = 1
 
while @i <= (select max(ordinal_position) from information_schema.columns where table_name = @table_name)
begin
   select @column_name = column_name from information_schema.columns where table_name = @table_name and ordinal_position = @i
   set @sql = @sql+'(select ['+@column_name+'] from '+@table_name+' where ID = (select max(ID) from '+@table_name+' where ['+@column_name+'] IS NOT NULL)) as ['+@column_name+'],'+char(13)+char(10)
   set @i = @i + 1
end
 
print @sql
exec(@sql)
--
-- method 2 - a bit cooler, using a concatenation technique...
--
declare @table_name varchar(200)
set @table_name = 'customers'
declare @sql varchar(max)
declare @i int
 
set @i = (select max(ordinal_position) from information_schema.columns where table_name = @table_name)
set @sql = replace((select '(select ['+column_name+'] from '+table_name+' where ID = (select max(ID) from '+table_name+' where ['+column_name+'] IS NOT NULL)) as ['+column_name+']'+case when ordinal_position = @i then '' else ',#CRLF#' end [text()] from information_schema.columns where table_name = @table_name  order by ordinal_position for xml path('')),'#CRLF#',char(13)+char(10))
 
print @sql
exec(@sql)

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22913181
Apologies, do not do the exec (@sql) - the queries do have to have a 'Select' to begin with etc...
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 2000 total points
ID: 22913203
here you go, can now run them all the way...
--method1
declare @table_name varchar(200)
declare @column_name varchar(200)
declare @sql varchar(max)
declare @i int
 
set @table_name = 'customers'
set @sql = ''
set @i = 1
 
while @i <= (select max(ordinal_position) from information_schema.columns where table_name = @table_name)
begin
   select @column_name = column_name from information_schema.columns where table_name = @table_name and ordinal_position = @i
   set @sql = @sql+'(select ['+@column_name+'] from '+@table_name+' where ID = (select max(ID) from '+@table_name+' where ['+@column_name+'] IS NOT NULL)) as ['+@column_name+'],'+char(13)+char(10)
   set @i = @i + 1
end
set @sql = 'Select ' + left(@sql,len(@sql)-3)
print @sql
exec(@sql)
go
--method2
declare @table_name varchar(200)
set @table_name = 'customers'
declare @sql varchar(max)
declare @i int
 
set @i = (select max(ordinal_position) from information_schema.columns where table_name = @table_name)
set @sql = 'select ' + replace((select '(select ['+column_name+'] from '+table_name+' where ID = (select max(ID) from '+table_name+' where ['+column_name+'] IS NOT NULL)) as ['+column_name+']'+case when ordinal_position = @i then '' else ',#CRLF#' end [text()] from information_schema.columns where table_name = @table_name  order by ordinal_position for xml path('')),'#CRLF#',char(13)+char(10))
 
print @sql
exec(@sql)

Open in new window

0
 
LVL 30

Author Comment

by:Alexandre Simões
ID: 22916149
Thanks! This is good stuff.

I'm working in your 2nd method and changed it a bit so I don't have to pass in the identity column.
As usually each table have it's own identity column name it would be a overhead to query the database for the identity column name before actually calling your code.

I'll be running more tests to better identify the best option.

Once again, thank you all.

Alex
declare @table_name varchar(200)
set @table_name = 'Test'
declare @sql varchar(max)
declare @i int
declare @idcolumn_name varchar(50);
 
-- get identity column name
select @idcolumn_name = column_name
from information_schema.columns
where
table_schema = 'dbo' and table_name = @table_name
and columnproperty(object_id(table_name), column_name,'IsIdentity') = 1
order by table_name 
 
if (@idcolumn_name is not null)
begin
	-- generate sql
	set @i = (select max(ordinal_position) from information_schema.columns where table_name = @table_name)
	set @sql = 'select ' + 
		replace((
			select '(select ['+column_name+'] from '+table_name+' where ' + @idcolumn_name + ' = (select max(' + @idcolumn_name + ') from '+table_name+' where ['+column_name+'] IS NOT NULL)) as ['+column_name+']'+
				case when ordinal_position = @i then '' 
				else ',#CRLF#' end [text()] 
			from information_schema.columns 
			where table_name = @table_name	
			order by ordinal_position for xml path(''))
		,'#CRLF#',char(13)+char(10))
	 
	print @sql
	exec(@sql)
end

Open in new window

0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22916331
Running a quick query on the information schemas is not too big an ask - will not add all that much overhead, and really not too many options... Will be interesting to see the final answer...

Cheers,
Mark Wills
0
 
LVL 30

Author Comment

by:Alexandre Simões
ID: 22922666
Running this query on a table with 155.000 records and 5 columns takes no more than 3 seconds on my laptop.
This will never happen as the query will never be on this amount of data but even on this kind of extreme scenario and taking in account the kind of query we're doing here, 3 seconds is a very good result.

I will run some more tests and close thread this shortly.

Thanks!
Alex
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 22922735
Seems reasonable. Not too many things you can do and retain that ultimate flexibility... As you said before there is a small price and it would seem that it really is a small price to pay... Have found laptops to occasionally outperform grunty servers - so - just be aware that the humble laptop is not a gaurantee that it is a lot slower than the server.
0
 
LVL 30

Author Comment

by:Alexandre Simões
ID: 22922860
Yeah, my laptop is not that bad at all and as this is meant to be applied on a SOA architecture as a logging service I really don't know how good will the "servers" be :)

I'm putting into battle the well known relational logging datamodel and a mirror datamodel approach.

Still, the 3 seconds delay is good for me as this isn't a "production" task.

Once again, thanks for your help.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Suggested Courses

615 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question