Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
SergioM
Posting Yak Master
170 Posts |
Posted - 2014-08-19 : 12:00:42
|
This is my first attempt at a temporal DB & also my first attempt with a client-facing database. Probably a stupid combination, but here I am.I have a db with 6 tables * BuyerFeedback * ComputerName * IPAddress * ProductData * SellerData * VendorDataFor any given Seller (SellerData) there are many products (ProductData). For any given Product there are instances of that product each time it was collected. It's (poorly) labeled VendorData. Lastly the BuyerFeedback table stores the criteria provided by the buyer. So the n amount of feedback received or y amount of reviewers. This will change each time it is polled.I want to create a query that selects all product data (ProductData) as well as the latest instance of VendorData, & the latest instance of BuyerFeedback & group that together with BuyerFeedback. This is what I have so far. It's pretty poor because I don't know how to get the top result per row. I either get the top results of all of the rows or all of the rows... Any help is appreciated! ThanksSELECT vd.ID, vd.ProductID, vd.IsPrime, vd.Price, vd.SellerID, vd.DateCollected AS 'vd_DateCollected', vd.ComputerName, vd.IPID, sd.SellerName, pd.ASIN, pd.ComputerName AS pd_ComputerName, pd.Country, pd.DateCollected AS 'pd_DateCollected', pd.ImageLink, pd.MSRP, pd.Title, ip.IP, ip.DateCollected AS 'ip_DateCollected' ,bf.Reviewers, bf.StarsFROM dbo.VendorData AS vd INNER JOIN (SELECT ID, AmazonSellerID, SellerName, DateCollected FROM dbo.SellerData) AS sd ON vd.SellerID = sd.ID INNER JOIN (SELECT ID, ASIN, Title, MSRP, ImageLink, DateCollected, ComputerName, IPID, Country FROM dbo.ProductData) AS pd ON pd.ID = vd.ProductID INNER JOIN (SELECT ID, IP, DateCollected FROM dbo.IPAddress) AS ip ON ip.ID = vd.IPID INNER JOIN (SELECT [ID] ,[ProductID] ,[Stars] ,[Reviewers] ,[DateCollected] ,[ComputerName] ,[IPID] FROM dbo.BuyerFeedback) AS bf ON bf.ProductID = pd.ID SQL Version - 2.56 MBhttps://www.dropbox.com/s/yjhkbz1pdo9664v/AmazonData.sqlZIP Version - 163 KBhttps://www.dropbox.com/s/couum1j2bnxwwpa/AmazonData.zip -SergioI use Microsoft SQL 2008 |
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-08-19 : 12:44:46
|
Please post your question like this: http://www.sqlservercentral.com/articles/Best+Practices/61537/Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
SergioM
Posting Yak Master
170 Posts |
Posted - 2014-08-19 : 12:54:55
|
quote: Originally posted by tkizer Please post your question like this: http://www.sqlservercentral.com/articles/Best+Practices/61537/
I'm not sure what I did which was different from the etiquette. It has the abstract, my own query & all of the table structures & data in a runnable SQL statement. Which part is not to your liking?-SergioI use Microsoft SQL 2008 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-08-19 : 12:59:35
|
I didn't click on your links to check (and still haven't). You might want to post the create table/inserts/expected output here in this post rather than on dropbox as some, like me, won't click on the links to download it. Not being lazy, just being security-cautious.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
SergioM
Posting Yak Master
170 Posts |
Posted - 2014-08-19 : 15:14:56
|
quote: Originally posted by tkizer I didn't click on your links to check (and still haven't). You might want to post the create table/inserts/expected output here in this post rather than on dropbox as some, like me, won't click on the links to download it. Not being lazy, just being security-cautious.
I hear you. It's just that it's 5K rows. I would happily paste it into the forum, but I thought it would not look good. Would Pastebin be better? Or should I just post it in here as is?Separately, I don't know if you're familiar with Dropbox, but it doesn't automatically download anything. It shows you a preview if possible. -SergioI use Microsoft SQL 2008 |
|
|
SergioM
Posting Yak Master
170 Posts |
Posted - 2014-08-19 : 15:33:48
|
Update: It's too large for pastebin & the forum gives me the following error when I paste it here:Request object error 'ASP 0104 : 80004005'Operation not Allowed/FORUMS/inc_header.asp, line 185-SergioI use Microsoft SQL 2008 |
|
|
SergioM
Posting Yak Master
170 Posts |
Posted - 2014-08-20 : 10:00:53
|
Can anyone help me?-SergioI use Microsoft SQL 2008 |
|
|
bitsmed
Aged Yak Warrior
545 Posts |
Posted - 2014-08-20 : 19:09:38
|
Maybe something like this:select * from (select vd.ID ,vd.ProductID ,vd.IsPrime ,vd.Price ,vd.SellerID ,vd.DateCollected as 'vd_DateCollected' ,vd.ComputerName ,vd.IPID ,sd.SellerName ,pd.ASIN ,pd.ComputerName as pd_ComputerName ,pd.Country ,pd.DateCollected as 'pd_DateCollected' ,pd.ImageLink ,pd.MSRP ,pd.Title ,ip.IP ,ip.DateCollected as 'ip_DateCollected' ,bf.Reviewers ,bf.Stars ,row_number() over (partition by vd.ProductID order by bf.DateCollected desc) as vd_rn ,row_number() over (partition by bf.ProductID order by bf.DateCollected desc) as bf_rn from dbo.ProductData as pd inner join dbo.VendorData as vd on vd.ProductID=pd.ID inner join dbo.BuyerFeedback as bf on bf.ProductID=pd.ID inner join dbo.SellerData as sd on sd.ID=vd.SellerID inner join dbo.IPAddress as ip on ip.ID=vd.IPID ) as a where a.vd_rn=1 and a.bf_rn=1 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2014-08-20 : 19:20:17
|
quote: Originally posted by SergioM
quote: Originally posted by tkizer I didn't click on your links to check (and still haven't). You might want to post the create table/inserts/expected output here in this post rather than on dropbox as some, like me, won't click on the links to download it. Not being lazy, just being security-cautious.
It's just that it's 5K rows.
We only need a few rows of each table and expected output using those rows. When I post a question, I typically give 5-10 rows per table and ensure there is linking data for the joins and that the expected output matches the data in the tables.Tara KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
|
|
|
|