I have the following query which is returning me total number of expected records :
select a.New_FirstName + ' ' + a.New_LastName as 'Account Name', a.New_MPRNNumber,MAX(b.New_readdate) as 'Last SC Read Date'
from AccountExtensionBase as a
left join New_meterreadingextensionBase as b on a.A...
Apparently we've got one tag, greatest-n-per-group, for what SO's got two: the same one and top-n-per-group, the former being for questions like the one I've just linked above and the other for questions like this:
I often need to select a number of rows from each group in a result set.
For example, I might want to list the 'n' highest or lowest recent order values per customer.
In more complex cases, the number of rows to list might vary per group (defined by an attribute of the grouping/parent record). ...
@JackDouglas I think that for some time there was just one tag there too and they decided to split the problem into two later.
I actually remember the time when I was associating both kinds with greatest-n-per-group, only to be later surprised when a new tag appeared.
So to me personally, greatest-n-per-group would be fine to represent both variations as it is now, I'm still used to it. I just think that there might be confusion for people coming from SO and noticing similar questions tagged differently.
My personal impression is that the difference isn't easily derived from the two tag names – I'd be fine with having them as synonyms. Speaking as a non-native English speaker, though, so...
It's true, though, that for people who "speak" Transact-SQL, top-n-per-group might have a clearer distinction from greatest-n-per-group because of an immediate association with the TOP clause.
Still, "greatest" kind of encompasses both "(single) max/min" and "top (n)" for me.
If the same book is/can be published by multiple publishers and you want to account for that, there should probably a separate table for... I don't know, publication? Maybe not the best name for it, but hopefully you get the idea. It would reference a book and a publisher and have a year or date of publication.
I mean if you want to distinguish between a book as a thing you can hold in your hands (in the "publication" sense) and a book as a work of art. Since you've got ISBN in Book, probably it's redundant, just have Year of Publication in Book.
@JANORTS The "N" of ISBN stands for "Number" so the "No." bit of your attribute name is redundant.
A book can have more than one author.
The "Borrow By" relationship has attribute. Although your modelling tool allows this, in real-world implementations attributes become columns. Therefore "Borrow By" should be represented by an entity type, at least in the physical model if not in the logical one.
The librarian-issued-book binary relationship should really be a librarian-issued-book-to-person ternary relationship.
"No. of issued book" and "available or not" are denormalised, summarising information available elsewhere in your model (by counting other entities).
Can a person join more than one branch? Is it important to know it is the same person that has all those memberships? If so separate PERSON and MEMBER entity types would be useful.
@JANORTS the diagram you posted has a relationship called "ISSUE" between Librarian and Book. It means whatever you made it mean when you put it into your diagram.
I'd suggest you start by deciding what question you what your system to answer. For example "Who has book 'X' and when is it due back?" "Who uses the library most?" That will tell you what pieces of information you need to hold and what relationships you need to capture. Then you can build your ERD from them.
You can validate the database design by applying your original questions and seeing if the DB can, in fact, answer them.
@PaulWhite Interesting Q on SO I answered the best I could but there are probably more stuff to say about it if you feel up to it. Perhaps bring it over to DBA?
@JANORTS If that's a requirement on your system then that will have to be included in your model. Add those two entities with the relationship(s) you need.
@PaulWhite That is an answer an answer that belongs on DBA and I don't think it falls on deaf ears for that OP. One more vote and it is moved hither. Thanks.
I have a problem understanding why SQL server decides to call user defined function for every value in the table even though only one row should be fetched. The actual SQL is a lot more complex, but I was able to reduce the problem down to this:
select
S.GROUPCODE,
H.ORDERCATEGORY
from...
Hypothetically speaking, assume you have master/model/tempdb all sitting on C and there's about 5GB free. The machine has two other drives: one mostly reserved for mdfs and the other for ldfs
If you can't get a dedicated drive added for tempdb, would you just continue the pattern of having the system mdfs mixed with the others?
I seem to recall something about mixing read/write activities but foggy brain is foggy this morning
Also, for moving tempdb, that's just a matter of changing the location in the database properties and upon restart, the initialization takes care of itself
@billinkc All things being equal, I'd probably put tempdb on the mdf or ldf drive, whichever was least busy or had enough space. 5GB on C doesn't seem like much, and I wouldn't want a full C to bring down the OS.
If the ldf drive is otherwise the best choice, I wouldn't be afraid of messing up its access pattern, assuming there's more than one active ldf on the drive already, the access pattern likely won't be sequential anyway. I suppose I'd lean toward the mdf drive though a dedicated drive would be better.
anyone feel like upvoting a tag synonym? query-optimization should point to optimization . The tag wikis are almost the same; both tags currently have questions.
@PaulWhite and @ypercube I'm good with optimization being for general system optimization; however the tag wiki needs to be updated, and the questions that are currently tagged with optimization may need to be retagged query-optimization
so, chicken/egg question: retag the 510 questions tagged with optimization to query-optimization and then change the tag wiki? Or change the tag wiki first? I suspect the tag excerpt for optimization should reflect that the tag is NOT for query optimization.
optimization has this in the tag excerpt:
In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.
However, there is another tag, query-optimization which is quite similar:
Query optimization is a function of...
I think my question is pretty clear
I would like to Merge customer_id from table user to table cust where
customers_firstname AND customers_name match
And I attached two images
In fact it was answered immediately
it was upvoted
someone edited my question as code
I would like to Merge cu...
optimization has this in the tag excerpt:
In the context of a database, optimisation refers to the process of the query optimiser selecting an efficient physical execution plan.
However, there is another tag, query-optimization which is quite similar:
Query optimization is a function of...
I have a problem understanding why SQL server decides to call user defined function for every value in the table even though only one row should be fetched. The actual SQL is a lot more complex, but I was able to reduce the problem down to this:
select
S.GROUPCODE,
H.ORDERCATEGORY
from...
> Given the small cardinality estimate assigned to the UDF apply, the n-ary join expansion heuristics unfortunately reposition it earlier in the tree than you would wish.
We have a simple, single table, LinqToSql query running against a SQL Server 2008 database. Every so often the query locks up and stops performing. Clearing the execution plans for the table is the only remedy we've found for the issue so far.
Query is as follows:
using (var context = new MyD...
If I'm understanding you correctly what you're looking to do should be quite simple.
I'm not entirely sure how you're setting @dat1 but for this to work you will need to set that as a SSIS variable and for this example I'll assum it's a date. Then you need to create your query as a variable and ...
Preferably from @billinkc because it's SSIS related.