|
Yes I have same table and want sql query for counting left and right node
|
|
|
|
|
Hi all,
Someone once posted here an excellent self join code on aggregates but just couldn't find it. The problem is similar as follows:
PriKey Field1 Field2 Field3 Field4
1 54 Ninna 42 1
2 2 Sybil 53 2
3 6 Michelle 44 3
4 12 Abigail 47 4
5 67 Geneana 55 5
6 23 Abigail 40 4
7 21 Evelyn 41 6
8 6 Sybil 57 2
9 85 Ninna 57 1
10 3 Sybil 49 2
11 78 Evelyn 50 6
12 4 Michelle 51 3
I want to group by on Field4 and for each, I want the maximum in Field1 such that the output is as follows:
PriKey Field1 Field2 Field3 Field4
8 6 Sybil 57 2
3 6 Michelle 44 3
9 85 Ninna 57 1
6 23 Abigail 40 4
11 78 Evelyn 50 6
5 67 Geneana 55 5 I believe the solution involves a self join and the max aggregate function. Thanks in advance.
----------------------------------------------------------
Lorem ipsum dolor sit amet.
|
|
|
|
|
Hi,
Assuming the table is called "kh" and you want it ORDER BY Field1 you could try this. The syntax works with T-SQL (Sybase). You may need to amend it for your DB if different.
SELECT t1.PriKey,t1.Field1, t1.Field2,t1.Field3,t1.Field4
FROM kh t1 INNER JOIN (SELECT MAX(Field1) AS Field1,Field4 FROM kh GROUP BY Field4) t2 ON (t1.Field4 = t2.Field4)
WHERE t1.Field1 = t2.Field1
ORDER BY t1.Field1
I hope it helps.
Cheers,
Kevin
|
|
|
|
|
yes it did, thanks!
----------------------------------------------------------
Lorem ipsum dolor sit amet.
|
|
|
|
|
Hi,
We want to synchronize SQL 2005 database, of a client between their two
branches in different cities, For that i found Web Synchronization, is helpful to us.
But I found that While configuring Web Synchronization, i have to use Domain
Name, Static IP, SSL critificate, Which might be cosltly to our customers. So i
want to ask three questions,
1. Is Web Synchronization possible without configuring Domain Name?
2. If I got Static IP, Is that possible to use IP Address in Webserver Address, while
synchronization?
3. Can I use Self Signed SSL Certificate?
Thanks in advance....
|
|
|
|
|
Currently I am connecting to an Access database via ODBC and a CRecordSet. I know I can create an ODBC entry for SQL Server and continue to use CRecordSet. All I have to do is change the connection string. I have used ADO in the past to connect to SQL Server but I am tempted to continue to use CRecordSet. What is the best way to connect to SQL Server? If I continue to use CRecordSets will I have problems later. Will the connection be slower.
Any past experience advice would be apprecated.
Thanks
|
|
|
|
|
I would not use ODBC as it is another layer of abstraction on your DAL. I have not used it in a very long time. The question to me is ALO or OLEDB[^] having done some research I decided to stick with OLEDB.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Ado.NET is Cool for that 
see this http://www.dotnetfunda.com/articles/article103.aspx[^]
Vuyiswa Maseko,
Few companies that installed computers to reduce the employment of clerks have realized their expectations.... They now need more and more expensive clerks even though they call them "Developers" or "Programmers."
C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.tiyaneProperties.co.za
vuyiswa@its.co.za
www.ITS.co.za
|
|
|
|
|
select m.pharmacyid as MemberJoinedPharmacy,
tempTrans.pharmacyid as MemberFirstTransaction,
tempTrans.transactiondate,
m.datejoined,
m.cardno, m.title, m.firstname, m.lastname,
m.street, m.suburb, m.state, m.postcode,
m.lastsale, p.pharmacyname
from member m
join pharmacy p
on m.pharmacyid = p.pharmacyid
join (select top 1 * from transactioncurrent t
where m.memberno = t.memberno
and t.voucherno is null
order by t.transactiondate) tempTrans
where m.datejoined >= '2009-01-01'
and m.pharmacyid in (select ph.pharmacyid
from pharmacy ph
where ph.pharmacygroupid = 87)
and m.datejoined <= tempTrans.transactiondate
and m.pharmacyid <> tempTrans.pharmacyid
order by m.pharmacyid
|
|
|
|
|
By db engineer I assume you mean the database engine? If so, obviously, yes there is something wrong - what that may be we can have very little idea without any other information.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Hi Bob,
thanks for ur reply, already worked out...
|
|
|
|
|
What sort of syntax error? Where is it? These will be stated in the error message.
|
|
|
|
|
thanks mate, already worked out....
|
|
|
|
|
i tried to drop the primary key of the table using
alter table customer drop primary key but it says
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'primary'.
what might be the problem
|
|
|
|
|
try this
alter table customer drop constraintname
constraintname is the name of the primary key constraint
|
|
|
|
|
I have the same problem too. What are you suppose to use.
|
|
|
|
|
Hi
What is the best version of sql server 2008 for my needs
1. all graphics function for make tables and query and so on...
2. database of over 2 million records
3. 30-40 users that work on the database
is the express edition fit for me ?
thank's in advance
|
|
|
|
|
Hi,
IIRC SQL Server Express is limited to 4GB of data.
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
I have an application I will call App1 which the customer access it's own database in an instance of SQL server which we have over 200 identically customer databases (identical schemas). However, we use a single login via web services that has admin rights over the entire database. It is the same for all customer databases. I want to reduce risk by lowering rights. APP1 only accesses the customer database via stored procedures. We also have several functions that we use which are called in the stored procedures. We do not use any views. We also created an ad hoc reporting app that I will call App2. App2 performs SELECT queries directly from the database tables. We do not update tables from App2. What is the simplest way to handle this. I would assume a role (I believe that would to be an application role) at the database level with execute permissions for App1. For App2, it would be the same except it would be SELECT ONLY, I would not allow it to have execute ability on the stored procedures. I think this is the best way but I would like some feedback before I go ahead and propose this change.
|
|
|
|
|
In reference to app2 the reporting app, we use procs to service 98% of our reporting requirements. ALL reporting procs are prefixed with rs and there is a standard SQL user with execute assigned to only those procs and select on all tables and views. We have some VERY complex reporting requirements.
We then have a script that goes through the database and assigns the correct rights for reportuser which the report dev runs whenever he publishes a new report.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hai all,
In my database table i have a data in a table like below
X 102
Y 101
Z 123
X 142
Y 245
Z 625
X 234
Y 432
Z 111
How can i get the data from table kike below.
X Y Z
102 101 123
142 245 625
234 432 111
Please help me how to write select query for below.
Thanks in advance,
cheers,
sekhar
modified on Saturday, April 4, 2009 7:26 AM
|
|
|
|
|
Hi,
You need to use SQL 2005's PIVOT statement. Have a look at examples here[^].
Regards,
Syed Mehroz Alam.
My Blog
My Articles
Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein
|
|
|
|
|
Hi,
I have a cte query that returns a hierarcy of content and subcontent shown below:
Id PId Name
----------------------------
1 0 HomePage
2 1 AboutUs
5 2 WhatWeDo
7 5 Alternate
8 5 Another one
10 8 Second
11 8 third
4 1 ContactUs
6 4 TotalItems
3 0 Testing
I want to take this result set and generate a Xml like the one shown below:
<ContentItems>
<Content Id='1' PId='0'>
<Name>HomePage</Name>
<Content Id='2' PId='1'>
<Name>AboutUs</Name>
</Content>
</Content>
....
</ContentItems>
I have tried using the FOR XML but it it seems to just put all the nodes under <contentitems> node. Any suggestions is appreciated. Thanks.
|
|
|
|
|
How to get the time difference between two dates in SQl?
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
