« first day (1624 days earlier)      last day (1639 days later) » 

8:04 AM
In this question
Q: Retrieving a record for the last date received and returning the value of another column in that row

JayI 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...

is the query missing a GROUP BY or is there an "implicit GROUP BY" switch in T-SQL I am yet to learn about?
Definitely missing a GROUP BY, over an OVER()
8:22 AM
Ah yes, it could be an OVER instead, although less likely perhaps.
8:47 AM
Apparently we've got one tag, , for what SO's got two: the same one and , the former being for questions like the one I've just linked above and the other for questions like this:
Q: Retrieving n rows per group

Paul WhiteI 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). ...

Do we need the same distinction here?
@AndriyM SO's top-n tag has a fraction of the number of Qs that their greatest-n tag has
I think I prefer the name though, maybe we should change it, make a synonym and keep them together?
@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, might have a clearer distinction from because of an immediate association with the TOP clause.
Still, "greatest" kind of encompasses both "(single) max/min" and "top (n)" for me.
9:15 AM
I'd much prefer [top-n-per-group]. Queries hypothetically tagged [least-n-per-group] are essentially the same.
Meta: how do I markup tags here?
not what I thought...!
Why is markup different depending on context? sigh
Some characters in the name may break it, e.g. apostrophe
9:33 AM
Ah, that's where I went wrong :)
1 hour later…
10:47 AM
Anybody here?
I am creating a simple database for LIBRARY MANAGEMENT SYSTEM
and I am not sure about one thing
When I am creating the database should there be some a relation for a borrowing also?
One borrowing = an event of borrowing a book
So I will have these relations: USER, BOOK, EMPLOYEE and BORROWING?
Something like that.
Why have both USER and EMPLOYEE?
10:53 AM
Because USER and EMPLOYEE have different attributes
I would rename USER to BORROWER or similar.
not all the attributes that USER has, also EMPLOYEE has and vice versa...
OK, I will rename it to BORROWER
USER is too generic a term IMHO.
Yes, that's true...
I was thinking about how to call it but didn't come up with anything better than USER
but BORROWER is better
Or something like that.
I like to name my tables in plural.
10:55 AM
So, I can't do the database without having an entity and a relation for BORROWING, right?
Maybe BORROWING should be LOANS
OK, LOAN(S) or BORROWING(S) ... it doesn't matter much...
Well, you could have a attribute on BOOK which indicates who has currently borrowed the book
but this means you'd be updating the book table all the time while the books themselves aren't really changing.
But, where will I have a due date and borrowing date?
And you can't see who borrowed the book when etc
In the borrowing table.
10:57 AM
Yes... That's why I need a table for BORROWING
But, in the ER model... there is no BORROWING right?
And probably a column "date_returned" which is NULL until the book has been returned.
Or something like that.
Yes, there is a borrowing in the ER model!
On that picture, there is no such thing...
"borrow by", right in the middle!!
So then the relationship will be expressed in table...
Now I get it
Yes! With foreign keys to both person (borrower) and book.
11:00 AM
But there is no entity for BORROWING in ER model...
So, I will create tables for all entities and all relationships?
Although you can get away with not making one for "issue"
But I want to have it
It is better I think
Since it has no attributes of its own, you can just make it an attribute/column of book.
I would make it a column in book. No need for a separate table in this case.
But where will I put borrowing date, due date and return date?
On borrowing.
I was talking about "issue".
11:03 AM
What is ISSUE?
Look at the ER diagram!
But, what does it mean?
That concrete librarian is responsible for concrete book?
Every book has its librarian who is taking care of the book?
Are you gone?
Year of Publication probably shouldn't be an attribute of Publisher
Yes that's true
But what is ISSUE?
I have no idea
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.
11:24 AM
@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).
But what does ISSUE mean?
or that Librarian issues or issued book...
Is it that the particular Librarian is responsible for particular Book?
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 DIDN'T make the diagram!
11:40 AM
@JANORTS Who did?
I have no idea
I just found it on the internet
Why did you post it?
Because it is pretty similar to mine
and it is usually the same
the diagram for Library Management System...
So, you don't know what the ISSUE means?
The word issue in this context means "The action of supplying or distributing an item for use, sale, or official purposes" (oxforddictionaries.com/definition/english/issue)
In this ERD it can be taken as "a librarian gives (issues) a book"
Are you trying to design a system for yourself or to understand that internet diagram?
Designing mine
I can make a picture and post it here
11:51 AM
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?
When you have a reasonably comprehensive list of use-cases and an ERD you can post a question on DBA.SE asking for comment.
I am posting it now
But I can't post it here
I will try to post it somewhere else...
I am not sure if it is readable...
The main problem that I have is that there can be 10 books with the same ISBN in the library.... So creating a table BOOK is not enough
It is important to differentiate between BOOK and BOOK_COPY
@JANORTS Not very legible.
What do you mean?
That you can't read the image?
12:05 PM
@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.
No, I can't read it clearly.
I will draw another one
Isn't there some software that is quick and easy?
Because that would better
To make it readable...
@JANORTS I'm going off line now. Post a question to the main site when you've got a good draft. I look forward to seeing there. Goodnight.
You don't know about any good software?
Is it crazy to have a table for PASSWORD?
I mean a password for borrowers of the library...
So it is gonna be a relationship BORROWER - PASSWORD, 1 - 1
I mean in real life could it be like that?
Is it secure?
Having non-encrypted password in a database system?
Is SQL and SQL server solution from Microsoft or Oracle secure enough, so that nobody, who is not supposed to... will get to see the password?
For example hacker from outside
or some hacker from inside...
12:20 PM
@JANORTS Try draw.io for an online option
Passwords should always be encrpyted in the database.
Can SQL Server do it for me?
So that one of the attributes in the database is encrypted?
It can. Whether or not you should depends on how secure you want your solution
Ypercube, I tried draw.io but it was horrible... Maybe it's only me and other people like it but it was really not very convenient to work with it...
1:06 PM
@JANORTS The passwords are usually salted+hashed before stored in the database.
If you want other data encrypted (say who borrowed what and when), that's a different security aspect.
@JANORTS The passwords are usually salted+hashed before stored in the database.
If you want other data encrypted (say who borrowed what and when), that's a different security aspect.
1:21 PM
@MikaelEriksson Added some details as a separate answer. Since it is an SO question, I doubt the OP is interested, but you did ask.
@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.
Yes I already voted because on balance I also feel it would be happier here.
For anyone with a spare migrate to dba vote available, this is the question in, er, question:
Q: Optimization issue with user defined function

JamesZI 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...

Migration complete. Thanks bluefeet.
2:27 PM
wow! a flurry of activity on The Heap so far
Flurry of activity is super effective!
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
well is this a production server?
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
@swasheck production and @billinkc are 2 things that don't go along
Truth! Buuuuut in this case, it's special. Like me
2:56 PM
@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.
@PaulWhite ^^ this. sorry, @billinkc ... was searching for hotels
@swasheck Hotels? Yikes, what'd you do now
sending my wife and son on a 10-year-old trip to NYC
It was funny though
3:00 PM
@billinkc i didnt find it to be all that funny
Probably funnier from the outside.
I think being inside the room would be quite awkward
I know, someone would probably run about in a darkened room and get a black eye
No, no one's that stupid or clumsy.
3:05 PM
The best defense is a strong offence
Words to live by.
@PaulWhite i know. i wasnt offended. just expressing (yet another in a long series of) disagreement with slick willie
Well that's ok then. Carry on.
That's it, no more fried chicken for either of you
3:06 PM
you're cutting my fried chicken in half???? wait. what's 0/2?
NaN chicken
better than 2/0
anyone feel like upvoting a tag synonym? should point to . The tag wikis are almost the same; both tags currently have questions.
Ahhh yeah. Full DBA privelages engaged!
@MaxVernon Done.
3:20 PM
"You do not have the required score on this tag to vote for this tag synonym." Not surprisingly.
3:32 PM
First one of Max's I've been able to vote on too.
3:48 PM
God I'm having a hell of a time setting up SQL Sentry
@PaulWhite Surprisingly I can vote but not sure I want to.
All of these stupid protections are killing me.
I always thought [opttimization] was for general/server/settings optimization issues while query-optimization was for, well, queries.
Shh shhh, it's the monkey with the gun
4:10 PM
@ypercube Maybe you're right.
@PaulWhite and @ypercube I'm good with being for general system optimization; however the tag wiki needs to be updated, and the questions that are currently tagged with may need to be retagged
Might be the right approach, on reflection.
so, chicken/egg question: retag the 510 questions tagged with to and then change the tag wiki? Or change the tag wiki first? I suspect the tag excerpt for should reflect that the tag is NOT for query optimization.
Big enough question/proposal it probably belongs on meta.
will do
4:34 PM
Q: Should the optimization tag be re-purposed away from "query optimization" and towards "general" system-level optimization?

Max Vernonoptimization 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...

Q: why is my question closed as unclear what you're asking?

yesitsmeI 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...

Q: Should the optimization tag be re-purposed away from "query optimization" and towards "general" system-level optimization?

Max Vernonoptimization 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...

@MasterDatabase why was that closed?
@Zane fat-finger? should have been closed as dupe
4:49 PM
Or too basic for the site
or MySQL
5:03 PM
Added my guess as an answer.
Brought a of tuna and forgot a can opener. Pair of scissors and a rock seemed to do the trick. lol
@PaulWhite Which answer would that be sir.
Q: Optimization issue with user defined function

JamesZI 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...

To the "why was my question closed" meta question.
@Zane Did you try paper? I hear that beats rock, but not scissors.
No I needed to beat can. Rock+Scissors beats can.
5:08 PM
Ah. The new rules :)
Although a napkin did make an appearance.
> 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.
TIL ^^^^
thanks Paulus Maximus
I often feel like adding a TL;DR after I answer, but then I think no, it took me time to write so only fair it takes time to read :D
Besides, I don't want to gain a reputation for being comprehensible.
you're far past that
Thanks :)
5:12 PM
indeed. glad you took it as a compliment; that's how it was intended
I take everything people say about me as a compliment. Easier and happier life that way.
Hahaha. Paul cuts someone off in traffic. Traffic guy:"Hey fuck you buddy" Paul: "Thank you"
That happened just the other day. They were such nice people!
5:29 PM
Well, but all kiwis are nice.
some can be kinda sour if they're not properly ripened
I've yet to meet a mean kiwi. In fact, I think they're so shocked by someone being mildly aggressive that I scare them when I go off on a rant.
I had a kiwi peck out my eye once. It was horrific
Does no one remember Richard?
I seem to recall him being a prick. If I remember correctly.
5:34 PM
I ate his liver with fava beans and a nice chianti
@billinkc what do you do that birds are always picking at you?
@Zane DickTheKiwi, if i recall correctly
@Zane Sorry homie, gotta take the fifth as the statue of limitations has not run out
@PaulWhite Was he native or a transplant?
Don't know.
Given the redo of his name to ... I assume Chinese characters, I'm going with a transplant
5:37 PM
Happy with that.
Where is it in NZ that you make fun of/don't care for? Wellington, Christchurch, somewhere else...
Maybe it's COTW that had a strong opinion on the matter...
@billinkc Auckland usually, but in fun, like with Aussies but slightly less so.
5:53 PM
that's awesome
Q: LinqToSql query is having intermittent problems

RandomWe 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...

i'd assume it's actually intermittently working
6:18 PM
had to be done. Didn't want it to stay.
6:49 PM
@PaulWhite I have heard about the JAFAs.
7:15 PM
Is anyone here using MARS?
I ate a mars bar once... Is that what you're looking for?
Did you like it?
Sometimes you feel like a nut?
I'm a peanut butter cup kind of guy.
Peanutbutter-relational mappers are the next big tech trend
7:28 PM
8:17 PM
Answer check.
A: How to pass parameter in select query(In with clause) and not in where condition in SQl Server SSIS Package?

ZaneIf 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.
Opened the answer, appreciated the freehand circles but many words there so will have to read after hours
8:32 PM
The word count is like 50
frickin novel
I fixed my typo btw.
lucky you
8:58 PM
Are you being a grumpy gus?
someone's got a case of the mondays
9:17 PM
But it's Tuesday
mind = blown
Not a difficult task when you're in the mix.
no doubt

« first day (1624 days earlier)      last day (1639 days later) »