SQL Server :: How To Limit Query Resultset To Unique Customers
Nov 10, 2010
I have the following query, the relationship is mutiple pictures per dog and multiple dogs per registered User. the query below will return dogs that belong to users that have not expired. the problem is that some users have many dogs and other users only have one dog listed, the query below will return a record for every dog. What I would like to do is return a random dog per user and only one dog per user each time the query executes. therefore if I have 10 users and 50 dogs I would like my quesry to only return 10 dogs one beloinging to each user, and the next time the quesry executes it would be nice if it once again return 10 dogs but a different 10 dogs 1/per user where it was able..
i want to get first picture of any album in photo table. i want get output table for show to the datalist control.i need to get albumid in output
so i create a sproc similar this:
create PROC USP_GETFirstPhoto @ALBUMID INT OUTPUT, @CATEGORY NVARCHAR(50) OUTPUT , @CAPTION NVARCHAR(50) OUTPUT AS SELECT @ALBUMID = A.AlbumID,@CATEGORY=A.Category,@CAPTION=TMP.Caption FROM Albums A CROSS APPLY ( SELECT top 1 * FROM Photos WHERE AlbumID=A.AlbumID )TMP order by A.AlbumID DECLARE @x INT,@y NVARCHAR(50),@z NVARCHAR(50) EXEC USP_GETFirstPhoto @x output,@y output,@z output select @x,@y,@z
but when i exec this proc i have a recorde not a result set
with this query i have resultset
SELECT * FROM Photos CROSS APPLY ( SELECT TOP 1 AlbumID FROM Photos GROUP BY AlbumID)TMP
I need to write a stored procedure to schedule a job that will send automatic email to customers based on some condition as follows:
1. I have two tables. One table is table1 like the following:
Name Date1 Date2 Date3 Rahim, Khan 29'10 30'11 25'08 Karim,Abedin 12'10 28'11 26'09 Fahad, Alam 26'10 12'11 25'10 Another Table is table 2 like the following:
Name Email Rahim,Khan rahim@yahoo.com Karim,Abedin karim@yahoo.com Fahad, Alam falam@yahoo.com In table 1 the date format is in format where 30'11 mean work week is 30 and 11 means last two digits of the year.
2. Now , I need to write a stored procedure that will check :
a) What is current work week and current year?
b) It will then determine the difference between the current work week (now 37) and work week mentioned in date (Date1, Date2 and Date3) columns from table1 and if for current year(it's 2010) at least one difference in each row is>=8 then it should send an automatic email to the corresponding email address of Name column using the relation between Table1 and table2. For example: For first record in Table1 the three difference is: 37-29=8, 37-30=7 and 37-25=12. There are two differences >=8, but only first difference is for year 2010 and third one is not for current year. But, since at least one difference in this row satisfies condition (That means >=8 for year 2010)hence email message should be sent to the address rahim@yahoo.com using relation of table1 and table2.
I know how to set a schedule job in SQL mail but I need this stored procedure to run this job on daily basis.
ing to show customers and their order totals. There are a few joins involved since the order items quantity and price come from 2 different tables. Here is the query:
select DISTINCT c.custnumber ordertotal = convert(money, (SUM(c.quantity) * d.Price)) from custOrders c left join purchaseorders p on p.cust_code = c.cust_code and p.purchno = c.ponumber left join purchasedetail [code]...
On one line the Code may be OX01 and shape Hexagon for example
Then the next line the code is still OX01 but the shape is Triangle.
What I want to do is be able to pull back data for every line that is equal to a unique Code column. in the above example OX01 Hexagon would be returned but not OX01 Triangle.
I've tried using SELECT DISTINCT but it brings back all records and doesn't filter them correctly.
I've considered having another column which could have Child/Parent data so that only the parent is returned but i'm sure there must be a better way of doing this.
I have set Private Memory limit of 200mb in IIS 7 for an application pool. The Private Working Set memory(Task Manager) for the application is always below 125mb but the number of page faults have increased a lot and application cache is getting cleared frequently after setting the limit.
I haven't set any limit on Virtual Memory.why the cache is getting cleared even when the Private memory used is below the allocated memory?
i desgin web site with sqlexpress 2008 now i want to convert it to sql standard enterprise i can or not? if it posible how i can convert my database from sqlexpress limit to sql server standard unlimit
How generate the unique no. 1,2,3 and so on .... on button click of each new user ..
the code mentioned below is a readwrite coding in vb.net ...
but the problem is it generate the same id for different users on button click event... but i want the no. of times button clicked the new ids will be generated
I have created a server product that is connecting to some social network servers and sending to data which status update etc. The server already authenticated to necessary social networks servers by users who is using this solution.
Actually, I have no problem at this time, but I think I will.
My server will be open a thousands of concurrent request to neccessary servers via Http with C# HttpWebRequest instance. I already know that It's possible to change concurrent request limits with below propery.
ServicePointManager.DefaultConnectionLimit AFAIK, this limit is max 100 evet you set more than 100. So, I will be faced with bottleneck problem with HttpWebRequest even change the DefaultConnectionLimit property of ServicePointManager.
I know this is a newbie question but, alas , that's what I am. How do I keep values in specifc sql server table unique, in other words how do I prevent any duplicates happening upon creating (inserting) a new table row?
See this is data stored in a date column of sql DB.(Just concentrate on months)1-12-20103-12-20101-01-20114-01-20119-01-20111-02-20115-02-2011-03-2011Now my requirements is select coding should fire and all the data should be available in dropdown listbut months and years should not be repeat. Means unique month with year should be display in dropdown list like belo
I need to know how to display the result of a select query in a datagrid or GridView with VB.NET?
Consider SELECT * FROM some_table. I don't know what columns the table has. Is there a way to just output the result to a table, with a dataset for example?
I have a query which returns multiple columns from the database using LINQ
var donors = from d2 in db.Donors where d2.Status == "Pending" select new { donorID = d2.donorID, bloodGroup = d2.bloodGroup, contactNo = d2.contactMobile, status = d2.Status };
now I want to display the results in different Labels accessing one column value from donors resultset.
ex:
Label1.Text = donorID; Label2.Text = bloodGroup; ...and so on
Can we use different resultset in our Report. Like if we have written two select statement in our SP, how can we use the different result set to bind them in our Report.
