Trap difference between two records...
-
hi, i need to compare two records of a table, the one with max date and the second highest one...so here need to somehow run a loop and trap the difference of each column and insert this diference in values in a hash table.. i can't get how to do this..could any one plz help me out here thanx
-
hi, i need to compare two records of a table, the one with max date and the second highest one...so here need to somehow run a loop and trap the difference of each column and insert this diference in values in a hash table.. i can't get how to do this..could any one plz help me out here thanx
deep7 wrote:
i can't get how to do this..could any one plz help me out here
What aspect? The getting the rows out of the database? The looping over the columns? What? Since you asked in the SQL forum, I'm gussing that it is the getting stuff out of the database aspect. To get the two rows in one result set use this:
SELECT TOP 2 *
FROM MyTable
ORDER BY [date] DESCTo get two result sets each with one row use this:
SELECT TOP 1 *
FROM MyTable
ORDER BY [date] DESC;SELECT TOP 1 *
FROM MyTable
WHERE [date] NOT IN(SELECT MAX([date]) FROM MyTable)
ORDER BY [date] DESC;Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
-
deep7 wrote:
i can't get how to do this..could any one plz help me out here
What aspect? The getting the rows out of the database? The looping over the columns? What? Since you asked in the SQL forum, I'm gussing that it is the getting stuff out of the database aspect. To get the two rows in one result set use this:
SELECT TOP 2 *
FROM MyTable
ORDER BY [date] DESCTo get two result sets each with one row use this:
SELECT TOP 1 *
FROM MyTable
ORDER BY [date] DESC;SELECT TOP 1 *
FROM MyTable
WHERE [date] NOT IN(SELECT MAX([date]) FROM MyTable)
ORDER BY [date] DESC;Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
Hi, thanks, but i wanted to know how to compare these two record which i get...its like i have to store the diffrence suppose the 'amount' value is not same, then store this in a hash table...how to compare the two records (I have to check for all columns) hope u understood.. thanks
-
deep7 wrote:
i can't get how to do this..could any one plz help me out here
What aspect? The getting the rows out of the database? The looping over the columns? What? Since you asked in the SQL forum, I'm gussing that it is the getting stuff out of the database aspect. To get the two rows in one result set use this:
SELECT TOP 2 *
FROM MyTable
ORDER BY [date] DESCTo get two result sets each with one row use this:
SELECT TOP 1 *
FROM MyTable
ORDER BY [date] DESC;SELECT TOP 1 *
FROM MyTable
WHERE [date] NOT IN(SELECT MAX([date]) FROM MyTable)
ORDER BY [date] DESC;Does this help?
"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question." --Charles Babbage (1791-1871) My: Website | Blog
Hi! This query shows how to do that:
select e1.EmployeeID, e1.BirthDate, cast(e1.BirthDate-( select top 1 e2.BirthDate from HumanResources.Employee e2 where e2.BirthDate You can try it with SQL Server's AdventureWorks sample DB. Rainer Stropek Visit my blog at http://www.cubido.at/Blog/tabid/176/BlogID/4/Default.aspx