September 8, 2010 at 12:54 am
Hi all!
Please help with modifying this code.
SELECT query1.*
FROM (SELECT bookid, Count(*) AS order_count
FROM borrow
GROUP BY borrow.bookid) AS query1,
(SELECT max(query2.order_count) AS highest_count
FROM (SELECT bookid, Count(*) AS order_count
FROM borrow
GROUP BY borrow.bookid) AS query2) AS query3
WHERE query1.order_count=query3.highest_count;
--===== Create the test table with
CREATE TABLE #Book
(
BookID text, primary key,
Title text,
Author text,
Genre text,
PublisherID text,
Status text
)
--===== Create the test table with
CREATE TABLE #Borrow
(
BookID text,
MemberID Number,
Borrowed_Date Date/Time,
Due_Date Date/Time
)
SELECT 'SELECT '
+ QUOTENAME(BookID,'''')+','
+ QUOTENAME(Title,'''')+','
+ QUOTENAME(Author,'''')+','
+ QUOTENAME(Genre,'''')+','
+ QUOTENAME(PublisherID,'''')
+ ' UNION ALL'
FROM Book
SELECT 'SELECT '
+ QUOTENAME(BookID,'''')+','
+ QUOTENAME(MemberID,'''')+','
+ QUOTENAME(Borrowed_Date,'''')+','
+ QUOTENAME(Due_Date,'''')
+ ' UNION ALL'
FROM Borrow
The above code generates search the BookID from "borrow" according to the most frequent value.
I need to use the BookID, of above sentence, to display "Book" Bookid,title,author,genre,publisherid.
Thanks
Leo
September 8, 2010 at 4:24 am
Please readand follow the advice given in the first article in my signature.
It's unlikely you can motivate a lot of people looking at image references to see table descriptions.
Please provide table def and sample data in a ready to use format.
September 8, 2010 at 6:09 am
Editted accordingly...
September 8, 2010 at 7:06 am
call4ljw (9/8/2010)
Editted accordingly...
Not really....
Sample data in a ready to use format and expected result, please.
September 8, 2010 at 7:55 am
LutzM (9/8/2010)
call4ljw (9/8/2010)
Editted accordingly...Not really....
Sample data in a ready to use format and expected result, please.
Something like:
CREATE TABLE Book (BookID int....)
CREATE TABLE Borrow (BookID int, ...)
INSERT INTO Book -- need enough of these to show your problem
INSERT INTO Borrow -- need enough of these to show your problem
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 8, 2010 at 8:09 am
WayneS (9/8/2010)
LutzM (9/8/2010)
call4ljw (9/8/2010)
Editted accordingly...Not really....
Sample data in a ready to use format and expected result, please.
Something like:
CREATE TABLE Book (BookID int....)
CREATE TABLE Borrow (BookID int, ...)
INSERT INTO Book -- need enough of these to show your problem
INSERT INTO Borrow -- need enough of these to show your problem
Ooops... forgot to reference the related link again... :blush:
Seems like the forum etiquette article will become the link of the month.
September 8, 2010 at 9:34 am
Like this?
September 8, 2010 at 10:09 am
call4ljw (9/8/2010)
Like this?
Almost.
Just try to run that code on a just created (empty) database in your test environment.
Most probably you'll get the same message like we do when we try to run your code in our test environments:
Msg 208, Level 16, State 1, Line 1
Invalid object name 'Book'.
You need to provide data in the form of
INSERT INTO #Book
SELECT 1,'Title1','Author1','Genre1','1','somestatus' UNION ALL
...
Also, your create table statement fails since there is no Number or Date/Time data type.
September 8, 2010 at 10:47 am
call4ljw (9/8/2010)
Like this?
You should give appropriate data types. Having a text field (which allows for 2gb) as a PK just isn't a good idea.
You might want to try right-clicking the existing tables in SSMS, and generate the create script.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 8, 2010 at 11:17 am
You should give appropriate data types. Having a text field (which allows for 2gb) as a PK just isn't a good idea.
It is not just a bad idea, SQL 2008 even prevents you from being so stupid by telling you explicitly:
Msg 1919, Level 16, State 1, Line 2
Column 'BookID' in table '#Book' is of a type that is invalid for use as a key column in an index.
September 8, 2010 at 11:37 am
You know, just a wild guess, but... you didn't even try to run that code you posted, did you? If you can't be bothered to find and fix syntax errors, why do you expect that we will?
CREATE TABLE #Book
(
BookID text, primary key,
Title text,
Author text,
Genre text,
PublisherID text,
Status text
)
Msg 8135, Level 16, State 0, Line 1
Table level constraint does not specify column list, table '#Book'.
--===== Create the test table with
CREATE TABLE #Borrow
(
BookID text,
MemberID Number,
Borrowed_Date Date/Time,
Due_Date Date/Time
)
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '/'.
Now how about posting some setup code that actually works? Appropriate data types would be nice too. I haven't seen many books or authors with names 2 billion characters long.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2010 at 11:51 am
LOL.
I was just thinking, if the BookID is 2GB as well, then taking an average of 2.5mm for the width of a single character on a barcode, that would amount to a barcode that is 5 billion milimeters long. That's 5000km :w00t:
Poor librarian who has to scan in more than one barcode label per year 😛
September 8, 2010 at 12:00 pm
Jan Van der Eecken (9/8/2010)
LOL.I was just thinking, if the BookID is 2GB as well, then taking an average of 2.5mm for the width of a single character on a barcode, that would amount to a barcode that is 5 billion milimeters long. That's 5000km :w00t:
Poor librarian who has to scan in more than one barcode label per year 😛
Time to switch to RFId. 😛
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
September 8, 2010 at 12:00 pm
Also, is this MS Access (as the forum name says) or SQL Server (as the thread title and contents imply)?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 8, 2010 at 12:56 pm
Assuming that this is an Access question....and using Access 2007.....
Access will allow you to create a primary key on an Access datatype of "text"...defaults to a field size in Access of 35.;...if you were to use Access upsize wizard to SQL this would translate to nvarchar(35)
In answer to the OP's question, and without any data...here's an idea to use in Access query designer ...switch to SQL view, and paste following (rename tables/fields accordingly)
regards gah
SELECT book.bookid,
book.title,
book.author,
book.genre,
book.publisherid
FROM book
INNER JOIN (SELECT TOP 1 borrow.bookid,
COUNT(borrow.memberid) AS countofmemberid
FROM borrow
GROUP BY borrow.bookid
ORDER BY COUNT(borrow.memberid) DESC) AS q
ON book.bookid = q.bookid;
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply