The Heap™ – Consultancy ©®

8:04 AM

In this question

0

Q: Retrieving a record for the last date received and returning the value of another column in that row

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

is the query missing a GROUP BY or is there an "implicit GROUP BY" switch in T-SQL I am yet to learn about?

Mark Sinkinson

Definitely missing a GROUP BY, over an OVER()

Andriy M

8:22 AM

Ah yes, it could be an OVER instead, although less likely perhaps.

Andriy M

8:47 AM

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:

34

Q: Retrieving n rows per group

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

sql-server t-sql greatest-n-per-group

Do we need the same distinction here?

Jack Douglas

@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 top-n-per-group though, maybe we should change it, make a synonym and keep them together?

Andriy M

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

Colin 't Hart

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?

Mark Sinkinson

not what I thought...!

Colin 't Hart

:-)

Why is markup different depending on context? sigh

Andriy M

[tag:name]

Some characters in the name may break it, e.g. apostrophe

Mark Sinkinson

9:33 AM

Ah, that's where I went wrong :)

Spam.... dba.stackexchange.com/a/104167/33638

JAN ORTS

10:47 AM

Anybody here?

Colin 't Hart

No.

JAN ORTS

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?

Colin 't Hart

Yes!

JAN ORTS

One borrowing = an event of borrowing a book

So I will have these relations: USER, BOOK, EMPLOYEE and BORROWING?

Colin 't Hart

Something like that.

Why have both USER and EMPLOYEE?

JAN ORTS

10:53 AM

Because USER and EMPLOYEE have different attributes

Colin 't Hart

I would rename USER to BORROWER or similar.

JAN ORTS

not all the attributes that USER has, also EMPLOYEE has and vice versa...

OK, I will rename it to BORROWER

Colin 't Hart

USER is too generic a term IMHO.

JAN ORTS

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

;-)

Colin 't Hart

Or something like that.

I like to name my tables in plural.

JAN ORTS

10:55 AM

So, I can't do the database without having an entity and a relation for BORROWING, right?

Colin 't Hart

Maybe BORROWING should be LOANS

JAN ORTS

OK, LOAN(S) or BORROWING(S) ... it doesn't matter much...

Colin 't Hart

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.

JAN ORTS

But, where will I have a due date and borrowing date?

Colin 't Hart

And you can't see who borrowed the book when etc

In the borrowing table.

JAN ORTS

10:57 AM

Yes... That's why I need a table for BORROWING

But, in the ER model... there is no BORROWING right?

Colin 't Hart

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!

JAN ORTS

On that picture, there is no such thing...

Colin 't Hart

"borrow by", right in the middle!!

JAN ORTS

OK

So then the relationship will be expressed in table...

Now I get it

Colin 't Hart

Yes! With foreign keys to both person (borrower) and book.

JAN ORTS

11:00 AM

But there is no entity for BORROWING in ER model...

So, I will create tables for all entities and all relationships?

Colin 't Hart

yes!

Although you can get away with not making one for "issue"

JAN ORTS

But I want to have it

It is better I think

Colin 't Hart

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.

JAN ORTS

But where will I put borrowing date, due date and return date?

Colin 't Hart

On borrowing.

I was talking about "issue".

JAN ORTS

11:03 AM

What is ISSUE?

Colin 't Hart

Look at the ER diagram!

JAN ORTS

I thought BORROWING = ISSUE

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?

Andriy M

Year of Publication probably shouldn't be an attribute of Publisher

JAN ORTS

Yes that's true

But what is ISSUE?

I have no idea

Andriy M

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.

Michael Green

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

JAN ORTS

But what does ISSUE mean?

or that Librarian issues or issued book...

Is it that the particular Librarian is responsible for particular Book?

Michael Green

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.

JAN ORTS

I DIDN'T make the diagram!

Michael Green

11:40 AM

@JANORTS Who did?

JAN ORTS

I have no idea

I just found it on the internet

Michael Green

Why did you post it?

JAN ORTS

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?

Michael Green

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?

JAN ORTS

Designing mine

I can make a picture and post it here

Michael Green

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.

Mikael Eriksson

@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?

Michael Green

When you have a reasonably comprehensive list of use-cases and an ERD you can post a question on DBA.SE asking for comment.

JAN ORTS

I am posting it now

But I can't post it here

I will try to post it somewhere else...

postimg.org/image/5donw0xpl

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

Michael Green

@JANORTS Not very legible.

JAN ORTS

What do you mean?

That you can't read the image?

Michael Green

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.

JAN ORTS

OK

I will draw another one

Michael Green

@Jan

JAN ORTS

Isn't there some software that is quick and easy?

Because that would better

To make it readable...

Michael Green

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