How can I write an sql statement that returns a list of how many students had lessons for each of the last 12 months? so I would expect output something like: +--------+----------------+ | Month | No of Students | +--------+----------------+ | Oct 08 | 12 | | ... | ... | | ... | ... | | Sep 09 |...
I have a table in Postgresql, I run a query on it with several conditions that returns multiple rows, ordered by one of the columns. In general it's: SELECT <some columns> FROM mytable <maybe some joins here> WHERE <various conditions> ORDER BY date DESC Now I'm only interested in getting the first and the last row from this query. I ...
Hey, I'm interested in what is the more efficient way of handling user data in my game. When someone is playing the game, data from the server will constantly need to be queried from the database. So I want to know if it is more efficient to keep querying the database or to store the data from the first query in a session and then keep...
I've been trying to interpret the answers to similar questions but haven't been able to make it work. I have a list of activities, and each activity as a list of participants. Here are the mappings: <class name="Activity" lazy="false"> <id name="ID"> <generator class="guid" /> </id> <list name="Participants"> ...
Supposed I have the following tables: Sailor(sid, sname, age) Boat(bid, sid) Each boat can have many sailors, and each individual sailor can serve on many boats. What I want to do is to find the boat with the highest average age of sailors. I can find the average age of the sailor on each boat with this subquery: SELECT b.bid, A...
I need to have "Where" Linq functionality in my own code. Let me explain: we have an application that lets users write the so-called "user code", which is code in C# that will be picked up by the app and compiled and run at runtime. In user code, I need to be able to specify an SQL condition. A special case is the condition on a date...
Ok so a guy at work has a little access database he uses to keep track of things. He has this form that he uses that already queries what he needs and produces the results on a form and that is really all he needs. One thing is that he has duplicates for every record that comes up with a different "Type" as a field "indentifier" (what I...
I'm quite new to setting indexes myself. I'm currently just experimenting with it to discover how it works and in what cases a database will make use of the index. I've got a simple table with 3 columns; an id, a name and a status. I've set an index on the name which is a CHAR(30) column. Against my expectations, MySQL ignores this inde...
I'm having a trouble getting a linq to sql query and was wondering if someone could help. I have these tables: 'Orders', 'OrderProducts', 'Products' and 'Users'. I want to get a breakdown of the count of how many products (contained in 'OrderProducts' by ProductID) each 'User' has ordered. The User's UserID is contained in the 'Orders...
I've a db that contains some accent characters and i've to do an accent insensitive search as fast as possible. I tried two solutions: query all possible accent combination. For example: perche perchè perché pèrche pérche pèrchè pèrché pérchè pérché rewrite LIKE operator where i first replace all accent characters with their respective...
Hi, I have a project that makes me confused. I am creating a 2nd hand sale car page for a saler. I have display and edit pages. In edit pages, user will insert a sale car record or edit an existing record. I mean Such a table: RecordID CARNAME Model Year KM PRICE (This Part is ok, easy; but details of car will be showe...
Hi, My company is in the process of rewriting an existing application from scratch. This application, among other tasks, performs complex SQL queries against order and invoice data to produce sales reports. The queries are built dynamically depending on which criteria are selected by the user, so they can be pretty complex if many crite...
Hi, I am having problem with named query parameter. My query is as follow: <sql-query name="getCustomer"> <![CDATA[ select * from customer where customerId=:custId and billId in ( :billIdList ) ]]> </sql-query> I am setting all parameters, but having problem in :billIdList whenever I am setting this parameter I am getting a Empt...
Hi all, in the example URL below, what would the QP and CP be? http://vivisimo.com/vivisimo/cgi-bin/query-meta?radiobutton=site&v%3Aproject=vivi&query=enterprise+search&submit.x=0&submit.y=0&submit=Submit Would QP be "query" and CP be "site"? Or, would it be "vivi&query" and "site&v%3Aproject"? Thanks for any help!...
CredTypeID is a number the CredType is the type of Credential I need the query to display the Credential in a drop down list so I can change the credential by selecting a new one. Currently I have to know the CredTypeID number to change the Credential. I just want to select it from a drop down list. Currently to change Betty Smith...
Should queries live inside the classes that need the data? Should queries live in stored procedures in the database so that they are reusable? In the first situation, changing your queries won't affect other people (either other code or people generating reports, etc). In the second, the queries are reusable by many and only exist in o...
I would like to know how to optimize the following SQL to let my server load faster and take low usage? I need to calculate the radius distance for a US ZIP Code to get the result, such as 50 miles from a particular ZIP Code ( using latitude and longitude to calculate ) and to getting how many other data ( e.g. others ZIP Code ) from my...
I have a programme listing database with all the information needed for one programme packed into one table (I should have split programmes and episodes into their own) Now since there are multiple episodes for any given show I wish to display the main page with just the title names in ascending and chosen letter. Now I know how to do th...
Using SQL Server 2000 query Select id, CONVERT(char(8), CASE WHEN DateAdd(Day, - DateDiff(Day, 0, OutTime), OutTime) > Normal_Outtime THEN Cast(Normal_Outtime AS datetime) ELSE DateAdd(Day, - DateDiff(Day, 0, OutTime), OutTime) END - CASE WHEN DateAdd(Day, - DateDiff(Day, 0, InTime), InTime) < Normal_Intime THEN Cast(Normal_Intime AS d...
I'm currently using the below code in one of my applications. It works great on a device running 3.0 but now always returns an Album count of 0 for each Playlist if the device is 3.1. Please let me know if I'm doing this incorrectly. I'm basically try to loop through the Playlists and count the number of Albums and Tracks for each one ...