Get latest for each - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Get latest for each

Author

Topic

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-05-23 : 03:18:24

I have a VBA-program that saves data from a 2D-array (or a normal Excel-range, if you know Excel) so that the source is a 2D-array and the result is a table. I have in my source a couple of hundred rows and 20 or so columns. I go thru each non-empty cell of the source and write the column name and the row name to a table, thus saving potentially a lot of space.

Now the saving works so that each time user hits save button, the liist is appended to the already existing list. This way this list (or a table) grows a lot and might end up having millions of rows.

Data I have in the table has ProjectID, ColumnName, RowName, Sum and SaveDate.

Now the problem I have is that what is the smartest way to read the data back to the table after it is saved? I mean, I need to fill the 2D-array with the latest data that is saved - it probably isn't smart to make thousands of queries, but how does it work then?

Something like
SELECT Distinct RowColCombination
WHERE SaveTime = Max(SaveTimeOfEachRowColCombination)
FROM whatevertableithappenstobe
?

Or do I just select the whole data, parse the ones needed from there and then discard the whole resultset?

I'll post some sample data a bit later if needed.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-05-23 : 04:00:42

Are the column names in the 2d-array fixed/always the same? If yes, then you should really drop the space-saving scheme and just dump the data directly in to the table instead, potentially with a lot of NULL-values. Depending on the datatypes they don't take all that much space and it will save you A LOT of headache. If you insist on using the "EAV-model" as it's called you should read a bit about its pro's and con's first: http://weblogs.sqlteam.com/davidm/articles/12117.aspx

If you are worried about the space issues you can look up "sparse columns" and "filtered indexes", but unless you're in the multi-million records I wouldn't think too much about it.

- Lumbago
My blog-> http://thefirstsql.com

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-05-23 : 04:12:11

No, both column and row names changes constantly. I mean, columns are actually months (05/2012, 06/2012 etc.) which are used for forecasting, so they actually shift instead of changing.

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-05-23 : 04:55:47

Hm, I'm not sure I get it completely but given your pseudo-query maybe something like this will help you along the way? ->



SELECT ProjectID, ColumnName, RowName, Sum, SaveDate
FROM (
	SELECT ProjectID, ColumnName, RowName, Sum, SaveDate,
		RowNum = ROW_NUMBER() OVER (PARTITION BY ProjectID, ColumnName, RowName ORDER BY SaveDate DESC)
	FROM whatevertableithappenstobe
	) as dt
WHERE RowNum = 1

- Lumbago
My blog-> http://thefirstsql.com

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-05-23 : 05:10:27

I was looking this kind of code earlier, but I didn't quite understand it. What is "OVER" and "PARTITION BY"?

But based on the earlier similar question I saw, I believe we're not far from what I'm looking for (haven't been able to test it quite yet, I'll do it shortly though).

Anyway, thanks already!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-05-23 : 06:17:19

The OVER is nothing to think about really, it's just syntax (there is no "under" or similar :)

The PARTITION BY is more or less the same as GROUP BY, it's how the row_number is grouped/decides when the row_number function should start counting at 1 again. It will become clearer once yo start fiddling with it.

- Lumbago
My blog-> http://thefirstsql.com

KilpAr
Yak Posting Veteran

80 Posts

Posted - 2012-05-24 : 09:56:45

Finally got to test it - it does exactly what I wanted. Thanks!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2012-05-24 : 15:51:22

Excellent :)

- Lumbago
My blog-> http://thefirstsql.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources