I am trying to return a result set that gives me the top 20 results for EACH group (PERILNAME in this case), not the top 20 results of the whole result set.
I have been able to create the results I want using UNION, but this is not practical when there is more than a few groups. I think it should be possible using a derived table, but I am not sure how to do it! Below is the code the returns the results I am after, any ideas how to achieve this another way?
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT) AS [Total Value],
COUNT(loc.LOCID) AS [Num Locs],loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Earthquake'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Windstorm'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Tornado/Hail'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
UNION
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT),
COUNT(loc.LOCID) ,loccvg.VALUECUR
FROM loc
INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID
INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID
INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE
WHERE LookupPeril.PERILNAME = 'Flood'
GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR
ORDER BY LookupPeril.PERILNAME, [Total Value]
I am trying to return a result set that gives me the top 20 results for EACH group (PERILNAME in this case), not the top 20 results of the whole result set.
I have been able to create the resluts I want using UNION, but this is not practical when there is more than a few groups. Below is the code the returns the results I am after, any ideas how to achieve this another way?
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT) AS [Total Value], COUNT(loc.LOCID) AS [Num Locs],loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Earthquake' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Windstorm' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Tornado/Hail' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Flood' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR ORDER BY LookupPeril.PERILNAME, [Total Value]
I need some help in returning the top 3 records in each group ie I have a table containing a list of stores with the address details and I need to return the top 3 records in each postcode area. The Postcode field contains the postcode area ie BN1, BN2 etc. Many thanks.
I have a table T (a1, ..., an, time, id). I need to select those rows that have different id (GROUP BY id), and from each "id group" the row that has the latest field 'time'. Something like SELECT a1, ..., an, time, id ORDER BY time DESC GROUP BY id. This is the wrong syntax and I don't know how to handle this.
It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.
Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?
if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?
Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?
Please have a look at the following two queries, the purpose of which is to find which ten users (represented by 'Username') have created the most records which contain the term 'foo':
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
WHERE FREETEXT(*, 'foo')
GROUP BY Username
ORDER BY [Count] DESC
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
JOIN FREETEXTTABLE (Options, *, 'foo', 500) ct
ON OptionID = ct.[KEY]
GROUP BY Username
ORDER BY [Count] DESC
They both produce the same result set. However, I am wondering which is more performant. At first glance, it would seem the first one would be. It doesn't involve a JOIN and should, therefore, be more efficient.
But this depends on how the FREETEXT expression is evaluated. My concern is that internally, SQL Server would generate an entire recordset based on 'WHERE FREETEXT(*, 'foo')', which could be thousands of records, and only then restrict this to the TOP 10 by COUNT.
If this does happen, then it would be better to join to a FREETEXTTABLE, where I can at least restrict the result set using the 'top_n_by_rank' parameter (which is set as '500' in this case, as this seems a good balance of performance against the likely number of duplicates I will get in my FREETEXTTABLE results).
So... I am worrying about this unnecessarily? Should I just use the simpler first version?
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
Not sure if this is possible, but maybe. I have a table that contains a bunch of logs. I'm doing something like SELECT * FROM LOGS. The primary key in this table is LogID. I have another table that contains error messages. Each LogID could have multiple error messages associated with it. To get the error messages. When I perform my first select query listed above, I would like one of the columns to be populated with ALL the error messages for that particular LogID (SELECT * FROM ERRORS WHERE LogID = MyLogID). Any thoughts as to how I could accomplish such a daring feat?
hello, i have two tables: Pictures and UserComments, both have PictureID column in second table i store each comment made by users at a specific picture like so: CommentID, UserName, PictureID, Comment, Date i am trying to make a stored procedure with @UserName input parameter witch returns Distinct Pictures whereon that user has commented (sorry for that whereon expression, is from dictionary and i don't know if it express what i want to mean) SELECT Pictures.OwnerName AS 'Owner', Pictures.Name AS 'Name of picture', COUNT(UserComments.PictureID) AS 'Comments made', Pictures.Image1 FROM Pictures INNER JOIN UserComments ON Pictures.PictureID = UserComments.PictureID WHERE (UserComments.UserName = @UserName) GROUP BY UserComments.UserName, Pictures.Name, Pictures.OwnerName, Pictures.Image1 if i use this statement it shows me the picture details whereon that user has commented and if he commented more than once on the same picture the result isn't duplicated but with that statement i can't order by UserComments.Date because i have to use GROUP BY UserComments.Date and the results will not be shown in pairs How can i order the results by Date Desc? sorry for my bad english, if you don't understand please tell me and i'll try to explain by examples please help me, thanks
Hello! This is my query:SELECT tblMessages.id, tblMessages.txtIngress, tblMessages.strSubject, tblMessages.txtMessage, tblMessages.postedBy, tblMessages.datePosted, tblMessages.intMessGroup FROM tblMessages INNER JOIN tblUsersToGroups ON tblMessages.intMessGroup = tblUsersToGroups.belongsToGroup INNER JOIN tblUsers ON tblUsersToGroups.userId = tblUsers.id WHERE (tblUsers.userName = @strUserName) GROUP BY tblMessages.intMessGroup, tblMessages.id, tblMessages.txtIngress, tblMessages.strSubject, tblMessages.txtMessage, tblMessages.postedBy, tblMessages.datePosted ORDER BY tblMessages.intMessGroupWhat I want to do is select the top 3 from each intMessGroup. In other cases where I'd want to Group By a computed column I could have used Rank, but since the column intMessGroup contains static values (message group id's) there should be some easy peasy way to do this, or?Thanks beforehand for any and all replies!Cheers!/Eskil
hai guys...am new to sql....plz help me out........ the below is my table structure.....Table Name : #temp pgm_main_category_id pgm_sub_category_id filepath 17 15 photo/Writer/Content/Sports/Basketball/bb1.jpg 17 16 photo/Writer/Content/Sports/Cricket/cricket1.jpg 17 17 photo/Writer/Content/Sports/BaseBall/base1.jpg 18 18 photo/Writer/Content/Nature/Forest/forest1.jpg 18 19 photo/Writer/Content/Nature/Tree/tree1.jpg 18 20 photo/Writer/Content/Nature/Flower/flower1.jpg 19 21 photo/Writer/Content/Gadget/Laptop/laptop1.jpg 19 22 photo/Writer/Content/Gadget/DigitalCamera/camer1.jpg 19 23 photo/Writer/Content/Gadget/Mobile/cybermbl1.jpg 17 24 photo/Writer/Content/Sports/Formula1/F1.jpg from this table i need the query output as below......for example: This is my expected output pgm_main_category_id pgm_sub_category_id filepath
17 15 photo/Writer/Content/Sports/Basketball/bb1.jpg 18 18 photo/Writer/Content/Nature/Forest/forest1.jpg 19 21 photo/Writer/Content/Gadget/Laptop/laptop1.jpg 17 16 photo/Writer/Content/Sports/Cricket/cricket1.jpg 18 19 photo/Writer/Content/Nature/Tree/tree1.jpg 19 22 photo/Writer/Content/Gadget/DigitalCamera/camer1.jpg 17 17 photo/Writer/Content/Sports/BaseBall/base1.jpg 18 20 photo/Writer/Content/Nature/Flower/flower1.jpg 19 23 photo/Writer/Content/Gadget/Mobile/cybermbl1.jpg 17 24 photo/Writer/Content/Sports/Formula1/F1.jpg if anybody have an idea about this query plz send me...... i need this immediately guys.....thanks in advance......regards janu
Hello,I have two tables:Student (containing columns: name, id, status, amount)Uplate (containing columns: id, student_id as foreign key, date, payement)Students make payment from time to time and that payment goes to Uplate.How to list (What is SELECT statement) to getevery studentits idstatusamounttotal payment made till today! (like sum all payement for this student)Thanks in advance.
I need to find the first 3 days that a product is scheduled in one of my tables. It may be scheduled on 12 different days, but I only want the first 3.
So, based on some sample data and this thread:
http://www.dbforums.com/t1115304.html
I was able to come up with a query that does what I need. However, I have no idea how it works. For one, what is Count(*) counting in the query?
insert into #tmpsched select '1/1/2001', 'abc' union all select '1/2/2001','abc' union all select '1/3/2001','abc' union all select '1/1/2001', 'def' union all select '1/2/2001','def' union all select '1/1/2001', 'ghi' union all select '1/1/2001', 'jkl' union all select '1/2/2001','jkl' union all select '1/3/2001','jkl' union all select '1/4/2001','jkl'
SELECT a.SCHED_DATE, a.PRODUCT FROM #tmpSched a INNER JOIN #tmpSched b ON a.product = b.product AND a.sched_date > = b.sched_date GROUP BY a.product, a.sched_date HAVING COUNT(*) <= 3 order by a.product, a.sched_date
drop table #tmpsched
Any explanation would be appreciated.
EDIT: Wanted first 3, not last 3. Changed "a.sched_date < = b.sched_date" to a.sched_date > = b.sched_date
I'm trying to find the most effective way to get the top N of each group in a group byI've already try the ROW_NUMBER() OVER PARTITION Method but it is still slow ...I've also checked the CROSS APPLY Method not really more efficient..Bu t My group is very simple and limited to one column...So Now I'm on the way to use a stored procedure make a loop of TOP n for each distinct element of my column
hi i have a database that has multiple records under the same id (each time a record is updated, it's assigned a date_value), i'm wanting one row to be generated using the data from the most recent date_value field. a sample of the database looks something like this: ID date_value data1 24 march info11 25 march info2 my statement looks like this:select max(date_value), data from table_name where data is not null group by id this seems to bring up the following error for all the fields not in the group by part of the code:Column 'COLUMN_NAME' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. what am i doing wrong? TIA!
I have this query. SELECT ForeignKey, MAX(Version) AS Latest_VersionFROM tblContentWHERE (Published = 'True')GROUP BY ForeignKeyORDER BY ForeignKey It works perfectly just like it is above. It returns 20 records choosing the maximum value in the version column for each value in the ForeignKey column. Here is a sample of the result set. ForeignKey Latest_Version 1 2 2 2 3 1 Then when I try to add other fields to the select and group by clauses I hit a problem. Here is the new query SELECT ForeignKey, MAX(Version) AS Latest_Version, TitleFROM tblContentWHERE (Published = 'True')GROUP BY ForeignKey, TitleORDER BY ForeignKey Now it is returning 22 records in the result set Here are the relevant records. ForeignKey Latest_Version Title 1 1 Title1 1 2 Title2 2 1 Title3 2 2 Title4 3 1 Title5 I know it is necessary to add each column in the select clause to the group by clause but that seems make group by not work at all. Am I going about this the wrong way. Basically I want to return only the ForeignKey records that have the highest version. This table does have a foreign key relationship with another table. Should I be doing this with a join instead somehow? Thanks, Laura
I have an aggregated table called T1 which looks like this:
Code:
Name Amount a 10 b -4 c 9 d 5 e 0 f 0 g 8 h 3 i 3
I need to write a query which selects the top 3 names by amount which have an amount greater than 0. The remaining names which have amounts greater than 0 should be grouped together as 'others' at the bottom. Finally I need to add a total row. So the final result I am looking for is:
Code: Name Amount a 10 c 9 g 8 others 11 total 38
The part I'm struggling with is how to collapse the remaining names into the 'others' group.
select 'PURCHASE' as EntityName, d.PartnerName, h.* from ( select MAX([TimeStamp]) [Data import], COUNT(1) [Numar de inregistrari], StartDate, EndDate, DistributorId from DataImport.PurchaseHistory group by DistributorId, StartDate, EndDate ) h inner join Partner d on d.PartnerId = h.DistributorId where d.Active = 1 order by DistributorId, StartDate desc, EndDate desc
I'm looking for some sql syntax that will return the last entry per group in a secondary table. MEANING: have 2 tables, one with names and the other with visits. I need to be able to display all the patients with there last visits.
I have for example a table with columns name, surname, id, ..., weight, age. I need to choose from each age group (GROUP BY age) entire record of the person who has the greatest weight. How to construct such a query?
CREATE TABLE #TestTable ( Pk INT, GroupID INT, Enabled BIT
[code]..
I need to write a select query that will retrieve any GroupID in which every record has an Enabled value of 1.In the example I've provided, only GroupID 1 and 3 will be returned since GroupID 2 has a record with an Enabled value of 0.What would be the most efficient way to write such a query?
Hi,I am bloody amateure and I was wondering if someone could help me editthe statement below so it groups the field SCDMASTER.SCTY_CLASS_CODEand creates one field called "total balance" equallingCUSTODY_BALANCE.OPENING_BALANCE + CUSTODY_BALANCE.DEPOSIT_AMOUNT -CUSTODY_BALANCE.WITHDRAWAL_AMOUNTSELECTCUSTODY_BALANCE.APPLICATIONCYCLEDATE,CUSTODY_BALANCE.ASSET_ID,SCDMASTER.SCTY_CLASS_CODE,CUSTODY_BALANCE.OPENING_BALANCE,CUSTODY_BALANCE.DEPOSIT_AMOUNT,CUSTODY_BALANCE.WITHDRAWAL_AMOUNTFROMFPMCAPSHIST.CUSTODY_BALANCE CUSTODY_BALANCE,CAPSREPORT.SCDMASTER SCDMASTERWHERECUSTODY_BALANCE.ASSET_ID = SCDMASTER.CUSIP_ID AND((CUSTODY_BALANCE.APPLICATIONCYCLEDATE={ts '2007-03-21 00:00:00'}) AND(SCDMASTER.SCTY_CLASS_CODE Not In('BILL','NOTE','BOND','CD','BD','CB','TINT','TPRN' ,'CA','YD')))Thanks,Andreas
I have a question about selecting only the first record in a group. Example: I have table A with primary key = 999. Table B has multiple records with primary key = 999. How can I match Table A 999 with the first occurrence of 999 in Table B, and then extract other field data (such as street address) from the Table B record. I have tried using the Count() function, but it seems that I can only do this using cursors.
Hello everyone, I've got a bit of an SQL mystery that I'm not sure how to solve. For some reason I just cant get my head around it. Here's the scenario: Table A: _____________ BidID - Int identity AuctionID - int BiderName - varchar(50) bidAmount - money ______________________ Now obviously each Bid will have a Unique ID using BidID but the other rows will contain multiple bids per user, on many different items possibly. BidID AuctionID BiderName BidAmount 1 4005 joeblow 100.00 2 4005 janedoe 101.00 3 4005 joeblow 107.00 4 4006 joeblow 100.00 5 4006 janedoe 105.00 6 4006 joeblow 106.00
I need to find out which Auctions JoeBlow is bidding on, but I dont need a table with Rows for every single one of his bids, just a distinct auctionID for his top bid so in this case the only thing returned would be 3 4005 joeblow 107.00 6 4006 joeblow 106.00 Any clues? I've been through sub querys, and stored procedures, and I cant get anything to work quite right. Thanks in advance for your help.
