|
For whatever reason, you could do the following...
select ceiling(cast(row_number() over (order by [field] asc) as float) / 3) as id, *
from [table]
Ryan
|
|
|
|
|
i think i know what are you trying to do. 
that table has a foregn key in id column, you should not do that.
study database structure, download samples from microsoft; northwind or other.
i dont know your database but it could be like this
my_client
id(*) f_name l_name
2 nelson paixao
3 helen hunter
25 brian ferry
...
my_product
id(**) name price
34 spoon 2€
35 fork 1.5€
67 dishwasher 356€
...
my_sales
id data id_client(*) id_product(**)
12 1/2/07 2 34
13 2/2/07 3 35
14 2/2/07 3 67
17 3/3/08 25 35
...
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
If I have a table:
TAB1
PKey Col1StrName Col2IntDebt Col3StrItem
1 Michael 2.00 Toothpaste
2 Josh 3.00 Shampoo
3 Karen 1.00 Softdrink
4 Josh 2.50 Soap
5 Michael 5.75 Lollies
6 Michael 0.25 Pen
7 Karen 3.25 Milk
If I do a group by
I can aggregate the debts to:
Name Debt
Michael 8.00
Josh 5.50
Karen 4.25
With the following code:
select
Col1StrName as Name, SUM(Col2IntDebt) as Debt
from
TAB1
group by
Col1StrName
I would like to include a column such that:
Name Debt Items
Michael 8.00 Toothpaste, Lollies, Pen
Josh 5.50 Shampoo, Soap
Karen 4.25 Softdrink, Milk
What is the aggregate function to use for the string?
TIA!
----------------------------------------------------------
"unzip; strip; touch; finger; mount; fsck; more; yes; unmount; sleep" - my daily unix command list
modified on Sunday, September 14, 2008 8:56 PM
|
|
|
|
|
There is no aggregate function to do what you want, it is a concatenation of the varchar fields, and there is no function available to do this. It would be nice if there was one as we see this requirements about once a a week here!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Can anyone teach me or show me some tutorials how to connect.. I need it badly..
|
|
|
|
|
Try this site
clickety[^]
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
For example, given a table, I want to group it every 10/20/30 records per group.
One medthod is that create one view for each cases, in the example, I need create 3 views.
Because the views for the same exception the count of records per group, how to unify them
(take the count per group as a parameter)?
Thanks.
system
|
|
|
|
|
You cannot use a parameter with a view, it needs to be a stored proc.
Having said that you can do something silly like store the value in a settings table and then create a view that uses that value in the where clause thereby allowing you to change the grouping from elsewhere. Seems like another weird requirement to me, but you often see them here.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi!
I am facing problem in INSERT statement of SQL 2005 express. Actually I want to save data from one table to another two tables using SQL statement. I want to do something like that, when I will press Create button from DataGride Column. The data of that row of that Registration table will insert in some other table like Account and Directory. I have tried but I cannot do it. Please help me if anybody can.
|
|
|
|
|
Structure of two tables must be same for code down below,if structure is not same then in select command you have to specify columns which corresponding with destionationtable.
Insert into DestionationTable select * from SourceTable
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
there ae three tables, i want to save data from one table to other two tables using SQL query. Please help me
|
|
|
|
|
He did, gave you the exact syntax for copying data from 1 table to another, surely you can work out how to copy from 1 table into 2 destination tables (hint call the script twice)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have give clue how to solve problem but sometimes some people are lazy and want ready solutions from others. 
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
do what blue_boy said.
it´s simply and fast but you can do also as following, may need in future
declare @id_t1 int
set @id_t1 = (select id from my_table1 where id = @whatever)
declare @f_name_t1 varchar(50)
set @_name_t1 = (select f_name from my_table1 where id = @whatever)
declare @l_name_t1 varchar(50)
set @l_name_t1 = (select l_name from my_table1 where id = @whatever)
insert into my_table2 (id,f_name,l_name) values (@id_t1,@f_name_t1,@l_name_t1)
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Thanks!
I think it will help me
|
|
|
|
|
When I run the following query
SELECT COUNT(*) AS Expr1
FROM Current
GROUP BY Amount
HAVING (Amount > .09)
I get two answers. I need to either add them together somehow in the SQL statement or get SQL to only return the total rather than placing them on separate rows.
|
|
|
|
|
SELECT .... FROM ... GROUP BY Amount will return the count(s) for each group. i mean
amount1 count1
amount2 count2
amount3 count3
amount4 count4
amount5 count5
...
Then, HAVING is applied to that. So, let's say that amount1 is > .09 and amount4 is > .09; the result becomes
count1
count4
which is the result you query returns.
If you want to get the count of rows whose Amount is > .09, you should
SELECT COUNT(*) AS Expr1
FROM Current
WHERE Amount > .09
Eslam Afifi
|
|
|
|
|
Hey Guys,
I have a table similar to the follwing structure:
AID BID
1 1
2 1
3 2
I need to get all AIDs where count(AID)>1 when grouping by BID
so in the above sample, query will only return AIDs 1 and 2 but will not return 3 because only one AID is in BID 2.
Your help is appreciated.
Modified:
is there a better sollution other then using:
AND EXISTS (
SELECT COUNT(AID)
FROM TableA
GROUP BY BID
HAVING COUNT(AID) >1
)
|
|
|
|
|
I don't know if this is better or not.
SELECT AID FROM TableA WHERE BID IN
(SELECT BID
FROM TableA
GROUP BY BID HAVING Count(AID) > 1);
Eslam Afifi
|
|
|
|
|
|
Glad to help.
Eslam Afifi
|
|
|
|
|
tblVisitor is a list of visitors that is linked to a record in the tblEmployee table and what I need to do is select the last visitor based on the ClockedIn column, which is a DateTime.
So my question is how do I insert this :-
SELECT TOP 1 * FROM tblVisitors WHERE intEmpID = tblEmployee.intEmpID ORDER BY ClockedIn DESC
into this SELECT statement :-
SELECT TOP (100) PERCENT employee.intEmpID AS ID, employee.strEmpID AS CardReference,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
Employee.strTitle + ' ' + Employee.strForename + ' ' + Employee.strSurname
END AS Visitor,
Visitor.Company,
Visitor.VisitorCardEmpId AS ResponsiblePersonnel,
CASE Employee.intStatus
WHEN 1 THEN
-1
ELSE
0
END AS ClockedIn,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
Visitor.TimeIn
END As ArrivalTime
FROM tblEmployee AS employee LEFT OUTER JOIN
dbo.tblVisitors AS Visitor ON Visitor.intEmpId = employee.intEmpID
WHERE (employee.strEmpID LIKE 'V%')
ORDER BY CardReference
So that the result is one row pre employee.
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Not sure I quite understand, but how about something like
.... Where Clockedin = (select max(clockedin) from tblvisitors)
i.e. you are selecting the latest clocked in time and using that to match back to your visitor.
BTW, why the SELECT TOP (100) PERCENT .., surely thats just selecting them all, but causing the server extra work?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Bob,
Thanks very much, you pointed me in the right direction. I did n't spot the SELECT TOP (100) percent, which was added by the View designer.
My final solution if anyone is interested :-
SELECT DISTINCT employee.intEmpID AS ID, employee.strEmpID AS CardReference,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
Employee.strTitle + ' ' + Employee.strForename + ' ' + Employee.strSurname
END AS Visitor,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
(SELECT TOP 1 Company FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC)
END As Company,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
(SELECT TOP 1 VisitorCardEmpId FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC)
END As ResponsiblePersonnelID,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
dbo.fnGetEmployeeName((SELECT TOP 1 VisitorCardEmpId FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC))
END As ResponsiblePersonnel,
CASE Employee.intStatus
WHEN 1 THEN
-1
ELSE
0
END AS ClockedIn,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
(SELECT TOP 1 TimeIn FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC)
END As ArrivalTime
FROM Tensor.dbo.tblEmployee AS employee LEFT OUTER JOIN
dbo.tblVisitors AS Visitor ON Visitor.intEmpId = employee.intEmpID
WHERE (employee.strEmpID LIKE 'V%')
ORDER BY CardReference
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Pleased to have been of use.
Bob
Ashfield Consultants Ltd
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
