nibe49

Hello againPlease read my solution again and that of rubynight as they are essentially the same.Let's try and picture the table you want - with values. I'll try to do it without any formattingTable RAINFields ( Year, Month, City, Amount )Shows rainfall by year and month and city.Year- Month--- City- Amount2007 January- citya 1.12007 February citya 1.62007 February cityb 0.52007 January--cityb 0.42006 January--citya 1.2Fields Year, Month, City form a composite primary key.You can add another dimension (eg day of month) by adding another column, and then that would also have to be part of the key.Does this clarify things?regardsnibe

Sorry about the formatting, I'm trying to represent a table with rows & columns & when I previewed it, it llooked ok.I hope you can make it out.regardsnibe

HiYou can represent any number of dimensions in a table.Just have 1 column per dimension and 1 column for the valuesThe dimension columns will form a composite key, but it would be useful to add a unique primary keyNote that the Dim cols are int or long. The value can be any type.Rows can occur in any order in the table eg <BR>Key Dim1 Dim2 Dim3 Value<BR>1 1 1 1 val_a<BR>2 1 1 2 val_b<BR>3 1 2 1 val_c<BR>4 2 3 2 val_d<BR> regardsnibe

Hi RyanaldoIn principle you can use a trigger that fires on a table insert. The trigger would need to carry out some action depending on a count of ids in the table. Triggers are a bit variable in syntax from 1 SQL vendor/product to another so I hesitate to write any code.The question to ask is what you want to do when transaction 6 comes along, you probably should not just ignore it.I think I would agree with pulpfiction that this is better controlled in the calling application.regardsnibe

Hi murfitUKYou can often do a subquery as a jointrySELECT * FROM category c LEFT JOIN details dON c.id = d.typeWHERE d.type IS NULLregardsnibe

Hi EmiNewid() is specific to SQL Server.It's ability to sort in a (pseudo) random manner as you have used it is known about, but not mentioned in BOL or any other official docs I can find. It was probably not intended for use that way.Random means ... random. With a small table you will get the same selections returned frequently because there are only a small number of choices, with a bigger table you may not get the same selection returned for a long time, maybe even longer than the age of the universe, but then again you may get 2 consecutive result sets the same.In any random method where you want each subsequent set to be unique you have to compare the present set with all previous sets and discard it if it has appeared before. This can't be done in a single query, it involves - for each selectionthe initial selectcomparison with previous selections (another select)an insert into the previous selection if this is a new set of values.It's not essential but I would recommend that you separate the random generation to work purely on a unique key ( but not a uniqueidentifier) rather than using newid().Hope this helpsNibe

Hi eggieYou appear to be trying to construct an index from a table called directory with headings taken from table category. which contains both headings and letters of the alphabet.If you are trying to do that, you need 3 select statements which you can UNION together. As the fields selected have different names you have to rename it using AS.Lets call the output field MYLISTTry something likeSELECT cat_a FROM category AS mylistUNIONSELECT cat FROM category AS mylistUNIONSELECT concat (bname,' ',btel) FROM directory AS mylist As all the fields as probably char or varchar you shouldn't need any conversion.Hope this helps - if not - post the table structures and required output as vijay suggested. The required output is important as I'm not convinced that I understand what you are trying to do.regardsnibe