Cant figure out this query

I have a table with multiple records.

Some have the same value in the 'subkey' field.
I want to select all the records from the table that have their highest MAINKEY.

So say there were 4 records in the table that has 3 fields (id, subkey and mainkey)

Each record has a unique id field but the subkeys are the same for the first two and the sub keys are the same for the last two while the Mainkey can be different.

So the tables looks sort of lLike this:

ID SK MK
1 10 2
2 10 3
3 25 2
4 25 3

I want to query and select one record for each subkey, but I want it to be record that has the highest mainkey. In this case, it would be records with ID 2 and 4.

I can not figure this out. :eek:

Any help would be GREATLY appreciated.This works...

SELECT [ID]
FROM yourtable T1
WHERE EXISTS (
SELECT SK, MAX(MK) AS MK
FROM yourtable T2
WHERE T1.SK=T2.SK
GROUP BY SK
HAVING T1.MK=MAX(T2.MK))|||select a.id, a.sk, a.mk from yourtable a
where a.mk in(select max(b.mk) from yourtable b
where a.sk = b.sk)|||Simpler even:

select a.sk, max(a.mk) as MK from yourtable a
group by a.sk

Cant delete records from DB .. says : Too many rows were affected by update.

Hi,

I've added multiple records with same info during practice. Now I"m trying to delete those records from SQL Server DB. but it says

"Key column information is insufficient or incorrect. To many rows were affected by update."

What to do, to delete these records?

Appreciated..if you post the code we can help you out but the error message you're getting means you're trying to update a row that has a duplicate. So, if your table had a primary key, you couldn't have a duplicate row and wouldn't have a problem.

hth|||Add a new column to your table and give each record a unique value for the column. Quickest way to do this is to add an Indentity column to the table (SQL Server will add the unique values). Delete the records you don't want and then you can remove the Indentity column if you want.

Can't Create cube

Is there is a way to create cube against database which tables get new records every few seconds?

I get these errors

Thanks in advance

Warning -2128674815 : Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_Log, Column: InsertDate, Value: 2/23/2007 9:22:32 AM.

Thanks in advance

Hello. I have not build anything like this but my guess is that you will have to use ROLAP for dimensions and Measure Groups.

The error you see probably depends on that you have a MOLAP cube where the dimensions have not been processed before the partitions/measure groups.

Keep your datamart in the relational engine is my recommendation. Five seconds is a very short batch window. I suspect that you have a cube directly on top of an OLTP-system.

Maybe Notification Services is a better choise than SSAS2005?

Regards

Thomas Ivarsson

|||

You'll need to update the time dimension and your data incrementally and "lock" the database updates while the update is being performed (to maintain consistency). This'll take some careful consideration of your partitioning scheme, to make sure you maintain a reponsive system.

How often do you need to update the cube? Could you consider doing the following steps:

a) materials the dimension information into a "Time" table

b) use a view "dbo_Log_view", that joins to the "Time" table to only return records that exist in the "Time" table. (Alternatively, you could do the same on the partition SQL statement, but I recommend abstracting it to the view.)

c) Process the cube / partition incrementally?

Note: You could set this up in a SQL Agent job, to run as a two step process automatically.

|||

Thank you guys.

I will have to carefully review all the options. Thank you for all suggestions.

Best Regards,

|||I think you need Integration Services for this. I haven't figured it out myself yet, but I need to make this too, and I came across functions in SSIS wich seemed to be usefull to accomplish this.

Regards,

Eyso