|
Hi all,
Please kindly suggest me a sql query to get all the second occuring of a row using foreign key.
For eg:
id fid priority updated
1 123 1 2008-06-29
2 123 2 2008-06-29
3 123 1 2008-06-25
4 124 1 2008-06-30
5 124 1 2008-06-29
6 124 2 2008-06-29
Using the foreign key fid and the updated how to get the second row for all the fid
Condition:
i. the priority should be 1
ii. the update value should should be the minimum than the first row
Sample o/p:
the output should be like this
3 123 1 2008-06-25
5 124 1 2008-06-29
Please help me
Thanks in advance
Know is Drop, Unknown is Ocean
|
|
|
|
|
you just want the MIN(updated) for each FID where priority = 1
so..
SELECT Id, Fid, Priority, Updated
FROM table a
WHERE Priority = 1
AND a.Updated = (SELECT MIN(Updated)
FROM table
WHERE Fid = a.Fid
AND Priority = 1)
might work...i havnt tested it...and i know its a bad way to do things...but its the first that came to mind...you can do it with a join, i just dont want to think that hard right now.
Please remember to rate helpful or unhelpful answers, it lets us and people reading the forums know if our answers are any good.
|
|
|
|
|
when I am trying to connect to remotely located sqlserver-2000 from asp.net (VS 2005) I am getting error msg "login failed to user XXXX".
I am using the following connection string
<add name="masterConnectionString">
connectionString="Data Source=ip;Network Library=DBMSSOCN
;Initial Catalog=dbname;Persist Security Info=False;User ID=user;Password=Xyz"
providerName="System.Data.SqlClient"></add>
Where am I going wrong ? our db server is located in 3rd party system. where we don't have any direct access to it to do any settings. what can I ask them to do?
Thank U all,
kiranmayi
|
|
|
|
|
ask the third party admin to thier sql server default username and password, Use that username and password in your conection string, it will definately work
|
|
|
|
|
Hi,
Does any one know if SQL Server 2008 Express is available yet? I tried looking for a download link but could find none.
Thanks
Brendan
|
|
|
|
|
|
RC0 version is available. Been using the February 2008 CTP for a while and does fine here.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
When executing a stored procedure in .NET code, are all statements inside the procedure considered to be a unit of work, or do I have to run this procedure inside a transaction (either by modifying the procedure, or by starting and comitting a transaction in .NET code)?
Thanks in advance!
|
|
|
|
|
In SQL Server, each individual statement, whether executed as a single batch, as a statement within a batch, or within a stored procedure, is independent. If you need the ACID properties to apply over multiple statements, you need to create a transaction.
In a stored procedure this is easiest to do with the BEGIN TRANSACTION statement. Use COMMIT TRANSACTION when you're done, to keep the results, or ROLLBACK TRANSACTION to discard them. Be aware that the transaction is not automatically rolled back for non-fatal errors, that is, errors that do not abort the batch. You should check @@ERROR after each statement to check whether there was an error.
DoEvents: Generating unexpected recursion since 1991
|
|
|
|
|
|
Hi,
I have an application which uses Pervasive SQL as a backend.
Is there any tool for Pervasive similar to Microsoft's Profiler for MS SQL server?
i would like to monitor the queries being executed behind the application?
Please help me.
Akash Agarwal
modified on Monday, June 30, 2008 9:27 AM
|
|
|
|
|
sql schema tblstudent(sid,sname)
i need a query like: select count(sid),count(sname) from tblstudent where sname=john;
Where count(sid) represent total number of student.
anyone help me plz...
|
|
|
|
|
Are you getting any error while executing this query? What's your requirement? Try modify the query as:
Select count(sid), count(sname) from tblstudent where sname = john group by sid, sname.
Success is the good fortune that comes from aspiration, desperation, perspiration and inspiration.
|
|
|
|
|
mdgkabir wrote: sql schema tblstudent(sid,sname)
i need a query like: select count(sid),count(sname) from tblstudent where sname=john;
Where count(sid) represent total number of student.
It looks like you just need to know how many rows with "john" are there in your table...
Try this:
select count(sid) from tblStudent where sName='john';
HTH
|
|
|
|
|
Hi guys,
I have created rdl file with 80 columns data table. I will hide the columns if data column is blank. My problem here is, if i export 1 page of data into pdf format then i am getting three pages of report. Here last two pages are blank.
Actually what is happening here is, I am selecting only 20 columns out of 80 columns. So report is showing only 20 columns and hiding remaining 80 columns. It is just hiding the columns but it is using that 60 columns space. Thats why i am getting two blank page.
Could any one tell me, how can i remove/avoid column space if data column is blank.
Thanks in advance.
Regards,
Prabhakaran.
|
|
|
|
|
Hello friends,
I want to create a temporary table at runtime. After that a cursor will be called to fetch necessary records. I want to insert each of these records into a temp table. How a single stored proc will do this? Any help..
Amit
|
|
|
|
|
Presumably SQL server and I hope 2005
Declare @Tbl Table(Field1 int, Field2 varchar(20))
Insert @Tbl
Select Field1, Field2
From SomeTable
You now have a table var with your data - use a while or cursor to do your processing
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi All
I thought I'd put this to you, my fellow devs, as I just cant decide the most efficient route! 
Suppose I have 2 tables, 1: id, productid, datedelivered, qty AND 2: id, productid, datesold, qty
I would like to end up having a result with: productid, monthvalue, openingbal, delivered, sold, closingbal
(monthvalue being something like 2008-01-01, i.e. each like is the stock recon for the month)
Now I know there are a few ways of doing this, one I thought of is with a cursor. But not being too clued up with the new features that 2005 provides I am not sure what the most efficient way would be ... any ideas? 
Thanks.
|
|
|
|
|
Francois Searle wrote: one I thought of is with a cursor
Stored procedures would probably be better performance-wise.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
Yes, I agree it should be within a SP, but I was referring more to the SQL within the SP, the methodology as such.
|
|
|
|
|
If you are going the standard sql route and it is slow, maybe query analyzer could shed some light for you where bottlenecks are.
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
|
A cursor is probably one of the worst ways you could do this. You should always attempt, where possible, to use set based queries as they are what database engines are designed to do.
|
|
|
|
|
I know cursors are the worst way, but currently my attempt at doing it using "normal" sql queries is running too long.
Surely this must be a common query? I mean there are so many scenarios where this type of "dataset" is needed.
|
|
|
|
|
I'm using ASP.NET and MySQL. I want to count all posts that were added before or in a specific month. The query is run in a loop and the results will be put into a graph but I get really strange numbers in some months.
"SELECT COUNT(*) FROM posts WHERE YEAR(timestamp) <= YEAR(?date) AND MONTH(timestamp) <= MONTH(?date)"
What is the correct way to do this?
|
|
|
|
|
Sunday8PM wrote: I get really strange numbers in some months
Like what? From a quick glance your query looks okay, cannot tell for sure without seeing what kind of data you have in the tables...
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Java | [Coffee]](https://www.codeproject.com/script/Forums/Images/coffee.gif)
