SQL Query - Highest Rated Or Least Voted Items
Jul 23, 2005
Hello everyone,
I'm trying to solve this problem but can't seem to figure out how to
start. I would like to create a rating system where people can vote
(1-5 stars) on randomly displayed items. The randomly displayed items
should either have very high ratings OR a very low number of ratings.
For example, only return items in the top 20th percentile *OR* items
with fewer than 5 votes.
The question is, how would I write an SQL query to return such a
result? Is it even possible? Should this be handled by my application
rather than the database?
For simplicity, let's assume I have the following table:
tbl_items
-----------------
item_id
item_name
avg_rating
num_votes
-----------------
Any help or pointers in the right direction would be greatly
appreciated. My apologies in advance if the solution is obvious and I
am clearly missing the point ;-)
View 5 Replies
ADVERTISEMENT
Mar 7, 2008
ALTER PROCEDURE dbo.GetMostRatedRecipe
(
@CurrentDate datetime
)
AS
SET NOCOUNT ON
SELECT *
FROM Recipe
WHERE TotalRating =
(SELECT MAX(TotalRating)FROM Recipe) AND published = 1 AND ReleaseDate <= @CurrentDate AND ExpireDate > @CurrentDate
This doesn't work good when the recipe having max total rating is not published & expired. I guess I need to first filter the recipes which are published & unexpired and then select the recipe having max total rating. But I don't know how to do that. Could anyone of you please help me doing this ?
View 1 Replies
View Related
Aug 9, 2005
What is the query to find a 5th highest salary.in emp table.i also use top1,top2,..but i don't get a result.what is new in sql server 2005.
View 11 Replies
View Related
Feb 6, 2006
Hi folks, sorry for the poor explanation.
Im using SQL 2000
I have a database that has a column named 'Initials' in a char in field
I want to be able to return in a query the highest entries if an indiviuals initials & count from the table, so it would display some like this
Initials Count
DRT 51
AMS 49
JJJ 21
PLI 10
Hope u can help, thanks in advance
View 2 Replies
View Related
Aug 14, 2013
structuring a query to give me the CurrBal per CardNo.
I have table that looks like this:
CardNo DepDate CurrBal DepAmnt TransNo
1 2013-06-04 11:50 AM 79 0 6
2 2013-08-05 15:34 PM 52 100 41
2 2013-08-05 14:11 PM -48 0 40
3 2013-07-09 13:52 PM 49 0 12
3 2013-07-22 13:51 PM 11 0 14
1 2013-06-12 10:46 AM 63 0 7
3 2013-07-15 14:04 PM 33 0 13
2 2013-08-06 15:05 PM 39 0 42
2 2013-08-07 13:38 PM 30 0 43
I am looking to order this table by CardNo and then TransNo but i only want the query to display the record with the highest TransNo for each CardNo. In other words discard the records with the lower TransNo for each CardNo.
My desired result should hopefully look something like this:
CardNo DepDate CurrBal DepAmnt TransNo
1 2013-06-12 10:46 AM 63 0 7
2 2013-08-07 13:38 PM 30 0 43
3 2013-07-22 13:51 PM 11 0 14
I am using SQL 2012 Express but would also like this query to work in SQL 2005.
View 7 Replies
View Related
Oct 9, 2007
Hi Everybody
I've one table named Student. Here is data
Name
Subject
Mraks
Prasad
English
80
Tushar
English
79
Sunil
English
78
Prasad
Geometry
80
Tushar
Geometry
81
Sunil
Geometry
79
Prasad
History
82
Tushar
History
81
Sunil
History
80
Now I want to write a query that displays student with subjects and marks who secured highest marks in each subject.
I want to output like this
Name
Subject
Mraks
Prasad
English
80
Tushar
Geometry
81
Prasad
History
82
So will anybody help me to write a sql query that acheive the same output
View 5 Replies
View Related
Apr 17, 2007
Hi: I'm try to create a stored procedure where I sum the amounts in an invoice and then store that summed amount in the Invoice record. My attempts at this have been me with the error "The multi-part identifier "items.TAX" could not be bound"Any help at correcting my procedure would be greatly appreciate. Regards,Roger Swetnam ALTER PROCEDURE [dbo].[UpdateInvoiceSummary] @Invoice_ID intAS DECLARE @Amount intBEGIN SELECT Invoice_ID, SUM(Rate * Quantity) AS Amount, SUM(PST) AS TAX FROM InvoiceItems AS items GROUP BY Invoice_ID HAVING (Invoice_ID = @Invoice_ID) Update Invoices SET Amount = items.Amount WHERE Invoice_ID =@Invoice_IDEND
View 3 Replies
View Related
Feb 25, 2015
I am struggling to come up with a set-based solution for this problem (i.e. that doesn't involve loops/cursors) ..A table contains items (identified by an ItemCode) and the set they belong to (identified by a SetId). Here is some sample data:
SetIdItemCode
1A
1B
24
28
26
310
312
410
[code]....
You can see that there are some sets that have the same members:
- 1 and 10
- 2 and 11
- 7, 8 & 9
What I want to do is identify the sets that have the same members, by giving them the same ID in another column called UniqueSetId.
View 8 Replies
View Related
Apr 10, 2015
I'm having an issue creating a report that can group & sum similar items together (I know in some ways, the requirement doesn't make sense, but it's what the client wants).
I have a table of items (i.e. products). Â In some cases, items can be components of another item (called "Kits"). Â In this scenario, we consider the kit itself, the "parent item" and the components within the kit are called "child items". Â In our Items table, we have a field called "Parent_Item_Id". Â Records for Child Items contain the Item Id of the parent. Â So a sample of my database would be the following:
ItemId | Parent_Item_Id | Name | QuantityAvailable
----------------------------------------
1 | NULL | Kit A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
4 | NULL | Kit B | 4
5 | 4 | Item 3 | 21
6 | NULL | Item 4 | 100
Item's 2 & 3 are child items of "Kit A", Item 5 is a child item of "Kit B" and Item 6 is just a stand alone item.
So, in my report, the client wants to see the SUM of both the kit & its components in a single line, grouped by the parent item. Â So an example of the report would be the following:
Name | Available Qty
--------------------------
Kit A | 15
Kit B | 25
Item 4 | 100
How I can setup my report to group properly?
View 6 Replies
View Related
Sep 2, 2014
I am doing parameterized queries from Visual Basic in Visual Studio.
I have a query where I want to do something like this.
Select * from people where city in (<list of cities>)
The problem is I want to build my <list of cities> in Visual Basic and pass it to the SQL as a parameter.
I know I can't do this:
Select * from people where city in (@ListOfCities)
Currently, I'm doing this by writing the <list of cities> out to a separate table, just so I can do the query.
Select * from people where city in (Select CityName from CityTable)
View 4 Replies
View Related
Feb 24, 2008
Hi,
I am a newbie, this is my first post (please go easy).
Iam at the moment trying to set up a query for someone looking for a property on an estate agents website.
From a drop down menu, the user can:
select an area (where they may like to live) from a list of areas.
select an amount of bedrooms from a list of bedrooms
select a minimum price from a list of prices
select a maximum price from a list of prices.
The query I worked out for this is as follows:
$data = mysql_query("SELECT * FROM property WHERE area like '$area' and bedrooms like '$bedrooms' AND price BETWEEN '$min_price' AND '$max_price'") or die(mysql_error());
This seems to work fine and shows all the properties that meet the criteria onto my webpage.
However, I then thought, someone may not care which area they live in and want to see all properties in all the areas, so I decided to add the option 'All areas' to my 'areas' list, I then did the same for the other lists, eg 'all bedrooms' option to my bedrooms list and so on.
I am now trying to write a query that incorporates where the 'all..' option is selected and have become very stuck!
Can someone set me off in the right direction for this.
I hope that makes sense?!?!
View 20 Replies
View Related
Jul 23, 2005
Let's say I have the following table:entry product quality1 A 802 A 703 A 804 B 605 B 906 C 807 D 808 A 509 C 70I'm looking for a way to find the average "quality" value for aSEQUENTIAL GROUPING of the same Product. For exmple, I need anaverage of Entry 1+2+3 (because this is the first grouping of the sameproduct type), but NOT want that average to include row 8 (which isalso Product A, but in a different "group".)I'm sure it can be done (because I can describe it!), but I'll be amonkey's uncle if I can figure out how. I would imagine it wouldinvolve some sort of running tally that references the next record asit goes... to see if the product type has changed. Perhaps use of atemporary table?Muchas gracias!!Cy.
View 9 Replies
View Related
Jul 23, 2005
I've never been the most articulate person in the world, so i thoughthe best way to illustrate the script I am looking for help with, is toprovide you with two tables and the results I require.Table 1ProductName1ProductName2QtyAAAA-12BBBB-13CCCC-14Table2ProductNameQtyAA2BB-13DD6Desired ResultsProductNameQtyDD6Regards,Ciarán
View 1 Replies
View Related
Jan 10, 2008
Hi,
I am new in SQL, I can not solve this case:
I have a product table like this
Name Qty Location
-----------------------------------
Item A 2 Warehouse 1
Item A 5 Warehouse 2
Item B 3 Warehouse 1
Item C 1 Warehouse 1
Item B 6 Warehouse 2
How is the sql command/query/store procedure
to generate result like this?
Name Qty
-----------------
Item A 7
Item B 9
Item C 1
Can anyone help me?
View 5 Replies
View Related
Oct 18, 2006
I'm not sure if this is even possible but can i pull out rows from two tables (that have a one-to-many relationship) but only if they satisfy a few conditions.
tblWine tblSources
ID Name ID WineID Source Price Status
----------------- -----------------------------------------
1 Le Dome 1 1 Smith 100.00 IB
2 Teyssier 2 1 Jones 110.00 IB
3 Muscat 3 1 Hill 100.00 DP
4 2 Smith 135.00 DP
5 2 Hill 125.00 DP
I only want to pull out row that contain the cheapest wine for their status. So the result would look something like this.
tblWineSources
WineID Name SourceID Source Price Status
-------------------------------------------------------------
1 Le Dome 1 Smith 110.00 IB
1 Le Dome 3 Hill 100.00 DP
2 Teyssier 5 Hill 125.00 DP
View 9 Replies
View Related
Jun 27, 2005
I have table1 with orderID and demographic info.
Table2 with orderID and items.
I would like to have a results display like this:
OrderIDDemographicInfo Item1Item2Item3....ect
One line per order. When I do a join I displaying all items in different rows.
View 1 Replies
View Related
Oct 17, 2014
I have this query currently:
select updatedb.callref, updatedb.updatetxt, updatedb.udsource, opencall.suppgroup
from updatedb
left join opencall
on updatedb.callref=opencall.callref
where udindex = '0'
and suppgroup = 'SUPPORT'
and (updatetxt like '%' + @Word + '%')
And opencall.status <> '17'This means that when they search for items and they separate each word it is "and" between each one.
They would like it to be more fuzzy with "and" and "or". How can I adapt this?
View 8 Replies
View Related
Nov 15, 2013
I have a dropdown list with checkbox and when I select multiple options and search, its returning only the last selected value in the grid. Here is the code I use it for search. Designation is the column where I bind its values to the checkbox.
Checkbox tag:
<asp:CheckBoxList ID="cblGroup" Style="vertical-align: baseline" runat="server" CssClass="chkbox">
</asp:CheckBoxList>
Select Query:SqlCommand cmd = new SqlCommand("select * from AppInvent_Test where Designation= '" + cblGroup.SelectedValue + "'", con);
View 2 Replies
View Related
May 5, 2015
Looking to write an query that will update a field for multiple items, like 1,500.
something like:
UPDATE INMAST
SET FPRICE = 111.11
WHERE
INMAST.FPARTNO = 'xxx'
only issue I'm having is a need to do a JOIN because there's one more condition that must be met from another table, i've tried this:
SET FPRICE = 111.11
JOIN INVCUR
ON
(inmast.fpartno + inmast.frev)= (invcur.fcpartno + invcur.fcpartrev)
WHERE
INMAST.FPARTNO = 'NRE'
AND
invcur.flanycur = 'TRUE'
but that is giving me an error around the JOIN
View 11 Replies
View Related
May 1, 2007
Use the Northwind database Products table as an example.Purchasing dept gets a report showing when inventory items on hand qty arebelow the reorder level.easy enough:Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevelfrom Productswhere (UnitsInStock < ReorderLevel)Results:ProductID ProductName SupplierID UnitsInStock ReorderLevel2 Chang 1 17253 Aniseed Syrup 1 1325It would be nice to know what other products are purchased from this samevendor in case other items are close to their reorder level.All products for Supplier ID 1Select ProductID, ProductName, SupplierID, UnitsInStock, ReorderLevelfrom Productswhere SupplierID = 1Results:ProductID ProductName SupplierID UnitsInStock ReorderLevel1 Chai 1 39102 Chang 1 17253 Aniseed Syrup 1 1325This shows there is 1 more product (Chai) that also comes from Supplier 1.Is there a way to show all items from a vendor when some of the items arebelow the reorder level without needing a separate query for each vendor?Thanks
View 4 Replies
View Related
Nov 29, 2015
I have a query that returns material(items) that are used in an event on a certain day.
SELECT C.categoryName, count(I.itemID) AS InMission
from items as I
RIGHT JOIN Categories AS C on I.categoryID = C.categoryID
INNER JOIN LinkMissionItem as LM on I.itemID = LM.itemID
INNER JOIN Missions as M on LM.missionID = M.MissionID
where '2015/12/19' BETWEEN M.freightLeave and M.freightReturn AND isReturned = 0
GROUP BY C.categoryName, C.categoryID
ORDER BY C.categoryID
There are a total of 20 categories and I would like all the categories listed in the result even though there are no items booked in a mission. At the moment, I can only get the categories that have items in that category booked in a mission. I hoped that the RIGHT JOIN on the categories table would do the trick but it doesn't.
View 4 Replies
View Related
May 12, 2006
I cannot find an easy way to DELETE items which are > 1 time in my table (i am working with MS SQL 2000)
idserialisOk
-------------------
2AAA1
3BBB0
5dfds0
6CCC1
7fdfd 0
8AAA0
9CCC0
I want to DELETE each Row IN
SELECT doublons.serial, Count(doublons.serial) AS 2Times
FROM doublons
GROUP BY doublons.serial
HAVING Count(doublons.serial)>1
and WHERE isOK = 0
in my exemple , after deleting, my table must look like
idserialisOk
-------------------
8AAA1
9CCC1
3BBB0
5dfds0
7fdfd0
thank you for helping
View 10 Replies
View Related
Oct 1, 2014
I am looking to filter the query using the free text predicate Contains for one of the column that have dimensions of the items. The query is something like
Select * from sampletable
where contains(filtercolumn, 'Near("*cm , *cm , *cm , *Kg.",10,True)')
Data in the filtercolumn is something like 64cm x 25cm x 35cm = 10Kg.
The query do not return any data.I also tried running the below query to see if cm* can be searcher for, but with no result again
Select * from sampletable
where contains(filtercolumn,, '"cm*"')
If I do something like below, it does return all columns that have 35cm within the text.
Select * from sampletable
where contains(filtercolumn,, '"35cm"')
View 1 Replies
View Related
Oct 1, 2015
I have a query that returns the data about test cases. Â Each test case can have multiple bugs associated to it. Â I would like a query that only returns the test cases that have all their associated bugs status = closed.For instance here is a sample of my data
TestCaseID TestCaseDescription  BugID BugStatus
1 Â Â Â Â Â Â Â Â TestCase1 Â Â Â Â Â Â Â Â Â Â Â 1 Â Â Â Closed
2 Â Â Â Â Â Â Â Â TestCase1 Â Â Â Â Â Â Â Â Â Â Â 2 Â Â Â Open
3 Â Â Â Â Â Â Â Â TestCase2 Â Â Â Â Â Â Â Â Â Â Â 11 Â Â Closed
4 Â Â Â Â Â Â Â Â TestCase2 Â Â Â Â Â Â Â Â Â Â Â 12 Â Â Closed
5 Â Â Â Â Â Â Â Â TestCase2 Â Â Â Â Â Â Â Â Â Â Â 13 Â Â Closed
How can I limit this to only return TestCase2 data since all of that test case's bugs have a status of closed.
View 3 Replies
View Related
May 26, 2006
Hi! i have a column named number(int),ref_number(char),date(date),creator(ch ar)...what would be my query string, if posible, to get the highest value for clumn 'number'? by the way im using SQL Desktop Engine... any inputs will be greatly appreciated...thanks in advanace!
View 3 Replies
View Related
Jan 20, 2005
Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Can anyone help?
View 3 Replies
View Related
Jul 20, 2005
Hi all,Given:f1 f2-- --1 a2 b3 b4 b5 c6 c7 dHow to get the highest count for field f2, output as:f2 f2count-- -------b 3
View 1 Replies
View Related
Jan 23, 2008
I have four columns that include numbers like: A B C D
I want to update D with highest value of A, B or C.
For example A = 1, B = 2, C= 3 then D should be 3.
Can you help me?
View 1 Replies
View Related
Feb 27, 2007
Can someone tell me what the highest value (number) is for an int data type in sql server 2005?
View 2 Replies
View Related
Jun 19, 2007
Hai frendz,
I am having a table named Employee(int EID, float Salary)...
Now I want to select the highest salary in the table and the query is-
"select top 1 EID, Salary from Employee ORDER BY Salary DESC"
Now I need to write a query which selects the second highest salary.
So how to achieve this?..
thanx
View 6 Replies
View Related
Sep 26, 2004
hello all,
i'm not new to SQL but i cant seem to get this right:
hope some one will:
how to find highest unique number of a certain column(val)for a specific name(name is in INPUT parameter)
i.e :
id | name | val
---------------
1 | name1 | 2.7
2 | name1 | 3.5
3 | name1 | 3.5
4 | name1 | 3.5
5 | name1 | 1.3
6 | name2 | 3.1
7 | name2 | 3.1
8 | name2 | 2.9
requested result:
if input param(name)=name1
result:
1 | name1 | 2.7
if input param(name)=name2
result:
8 | name2 | 2.9
hope some one can write the sql cmd for this
i'll be grateful !
thank you!
View 3 Replies
View Related
Dec 8, 2004
Got a table with salestatistics and I want to know what ItemNr sold most a certain day. Can anyone help me with the SQL?
View 1 Replies
View Related
Sep 21, 2004
Hello,
I have the following problem.
I have three Columns
name / version / package
I want to create a select query, that shows me all names and their actuall package. The actual Package is the record with the highest version number.
For Example my Database has the following entries :
name / version / package
name1 / 1 / package1
name2 / 1 / package1
name2 / 2 / package3
name3 / 1 / package2
The output should look like this :
name1 / 1 / package1
name2 / 2 / package3
name3 / 1 / package2
Has anyone a idea and can help me ?
Thanks in Advance
Mirco
View 4 Replies
View Related
