Database Discussion Boards

Re: Top 1 distinct or something like that

Paul E Davies3-Jun-11 3:33

3-Jun-11 3:33

This did it thanks.

apologies for the vagueness, well I knew what I meant.

To hopefully clear up any questions - the table can have many thousands of rows each one holds the details of application events used for auditing purposes.

There are about 40 event types that are logged (eventid) and the details column holds the data associated with a particular event

e.g

<AuditFragment xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <LogText>10.10.10.10 (10.10.10.10) POST http://server/page.aspx?ControllerType=InPatients</LogText>
  <LogTextToDisplay>10.10.10.10 (10.10.10.10) POST http://server/page.aspx?ControllerType=InPatients</LogTextToDisplay>
</AuditFragment>

this one describes a simple page view.

Other columns are things like hold details of user ids, timestamps etc but in this instance were not required I jut needed one example of each event type to test a reporting application.

Reading that I could have been clearer in my original post.

Thanks again everyone.

Re: Top 1 distinct or something like that

Simon_Whale3-Jun-11 1:32

Simon_Whale

3-Jun-11 1:32

Without sample data its hard to give you a best solution, give this a try

SQL

select distinct a.typeid, (select top 1 details from auditlog where typeid = a.typeid) [details]
from auditlog

Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch

SQL Server: One connection vs Connection Per Client

Eli Nurman2-Jun-11 5:59

Eli Nurman

2-Jun-11 5:59

Hi
When building a website, which method will increase performance, Using one connection in a global variable and using it to execute all queries or opening a connection for each user (Session)?

Please advice.

Re: SQL Server: One connection vs Connection Per Client

David Mujica2-Jun-11 6:14

David Mujica

2-Jun-11 6:14

Check this article out ...
ADO.NET Connection Pooling at a Glance[^]

It may help you understand what is going on with the web server and the database server.

Good luck.
Thumbs Up | :thumbsup:

Re: SQL Server: One connection vs Connection Per Client

jschell2-Jun-11 9:48

jschell

2-Jun-11 9:48

Normally the second.

But, again normally, there are far better reasons for doing that than performance.

Distinct + Count

JP_Rocks1-Jun-11 23:51

JP_Rocks

1-Jun-11 23:51

I want to find distinct country and count of each country from a country table.
How should I do?

Result:

Country | Count
Japan | 10
UK | 8
Canada | 12

***** Programme comme si dept soutien technique. est plein de tueurs en série et ils savent adresse de votre domicile. *****

Re: Distinct + Count

Ravi Sant1-Jun-11 23:56

Ravi Sant

1-Jun-11 23:56

select country, count(country) from countrytable
group by country

// ♫ 99 little bugs in the code,
// 99 bugs in the code
// We fix a bug, compile it again
// 101 little bugs in the code ♫

Re: Distinct + Count

JP_Rocks2-Jun-11 0:03

JP_Rocks

2-Jun-11 0:03

Thank You, I missed group by.

***** Programme comme si dept soutien technique. est plein de tueurs en série et ils savent adresse de votre domicile. *****

Re: Distinct + Count

JP_Rocks2-Jun-11 0:07

JP_Rocks

2-Jun-11 0:07

Is is not working for null country and does not give correct count
Hepl ?

***** Programme comme si dept soutien technique. est plein de tueurs en série et ils savent adresse de votre domicile. *****

Re: Distinct + Count

Blue_Boy2-Jun-11 1:54

Blue_Boy

2-Jun-11 1:54

Use isnull for your count field.

e.g

select country, count(isnull(country,0)) from countrytable
group by country

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.

www.cacttus.com

Re: Distinct + Count

SilimSayo2-Jun-11 2:30

SilimSayo

2-Jun-11 2:30

If country is null, it need not be shown.

I would say

select country, count(country) from countrytable
where country is not null
group by country

sql server hangs with multi queries

Eli Nurman1-Jun-11 20:26

Eli Nurman

1-Jun-11 20:26

Hi. I have a website (IIS) that clients make queries to the server which could take up to a minute to get the results. With that i have no problem. but when a different user(!) on IIS tries to query even a different table he will hang until the first user is responded. When i enter SSMS i can query and get immediate result. Does anyone have a solution for this?

I must mention, that the SQL Connection in the website is not unique per request or per client, it is one connection for all requests, any chance that this is the reason for the wait?

Thanks.

Eli

Re: sql server hangs with multi queries

jschell2-Jun-11 9:50

jschell

2-Jun-11 9:50

Eli Nurman wrote:
any chance that this is the reason for the wait?

yes.

Re: sql server hangs with multi queries

Eli Nurman2-Jun-11 9:58

Eli Nurman

2-Jun-11 9:58

Hi, Thanks for you reply. So how can i solve this? Why are queries via SSMS not affected?

Re: sql server hangs with multi queries

jschell3-Jun-11 8:15

jschell

3-Jun-11 8:15

Eli Nurman wrote:
So how can i solve this

Use more than one connection.

Eli Nurman wrote:
Why are queries via SSMS not affected?

Because it is using a different connection.

SQL SERVER Training Videos..

siva4551-Jun-11 19:48

siva455

1-Jun-11 19:48

Hi,
Please suggest me any website which offers free training videos for programming SQL server 2005 or 2008...

Re: SQL SERVER Training Videos..

Ravi Sant2-Jun-11 0:07

Ravi Sant

2-Jun-11 0:07

You can check following resources:

SQL Server 2008 Demo Videos and Webcasts[^]

SQL Server 2008 Videos[^]

SQL Server Training.[^]

SQL Server for Developers[^]

Some Channel 9 Videos[^]

// ♫ 99 little bugs in the code,
// 99 bugs in the code
// We fix a bug, compile it again
// 101 little bugs in the code ♫

Select Distinct by an Image Field

Vimalsoft(Pty) Ltd31-May-11 2:15

Vimalsoft(Pty) Ltd

31-May-11 2:15

i have an interesting question for you guys.

SELECT   S.SCHOOLID AS [SCHOOLID],S.SCHOOLNAME AS [SCHOOLNAME],SP.PHOTO AS [PHOTO]
FROM SCHOOLPHOTO SP
INNER JOIN SCHOOL S
ON SP.SCHOOLID = SP.SCHOOLID
WHERE S.SCHOOLID  IN (SELECT SCHOOLID FROM #SCHOOLS)

the Photo Field is an Image Datatype Field, i now you can use them in a Distinct or group by , How can i go by have that field there without casting it , because if i cast it it will not work together in Silverlight or any other image control.

Thanks

Vuyiswa Maseko,

Spoted in Daniweb-- Sorry to rant. I hate websites. They are just wierd. They don't behave like normal code.

C#/VB.NET/ASP.NET/SQL7/2000/2005/2008
http://www.vuyiswamaseko.com
vuyiswa@its.co.za
http://www.itsabacus.co.za/itsabacus/

Hash code

David Mujica31-May-11 2:48

David Mujica

31-May-11 2:48

How about generating a hash code for your image and store that value in your table along with the image. You could then use the hash code in your group by clause.

check this article out ...
http://www.vcskicks.com/image-hash.php[^]

It was the first thing that popped up in my Google search.

Good luck.
Thumbs Up | :thumbsup:

Re: Hash code

Vimalsoft(Pty) Ltd31-May-11 3:51

Vimalsoft(Pty) Ltd

31-May-11 3:51

I need a Solution in T-SQL

Re: Hash code

Pete O'Hanlon31-May-11 4:10

Pete O'Hanlon

31-May-11 4:10

What version of SQL Server are you using? If it's sufficiently advanced, you could apply this as a CLR Trigger.

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

Re: Hash code

Vimalsoft(Pty) Ltd31-May-11 4:35

Vimalsoft(Pty) Ltd

31-May-11 4:35

I am SQL 2008

Re: Hash code

Pete O'Hanlon31-May-11 4:57

Pete O'Hanlon

31-May-11 4:57

Then it's job done. You can use a SQL CLR trigger and store the hash when the image is inserted or updated.

Forgive your enemies - it messes with their heads

My blog | My articles | MoXAML PowerToys | Mole 2010 - debugging made easier - my favourite utility

Re: Hash code

Vimalsoft(Pty) Ltd31-May-11 5:25

Vimalsoft(Pty) Ltd

31-May-11 5:25

this did the Job

 ;with cteSchools as (SELECT DISTINCT S.SCHOOLID AS [SCHOOLID],S.SCHOOLNAME AS [SCHOOLNAME]
FROM SCHOOL S

WHERE S.SCHOOLID IN (SELECT SCHOOLID FROM #SCHOOLS))


SELECT S.SCHOOLID AS [SCHOOLID],S.SCHOOLNAME AS [SCHOOLNAME],SP.PHOTO AS [PHOTO]
FROM cteSCHOOLS S
CROSS APPLY (Select TOP (1) PHOTO from SchoolPhoto SP
WHERE SP.SCHOOLID = S.SCHOOLID
ORDER BY SCHOOLID DESC) SP

Thanks

Re: Hash code

Shameel31-May-11 5:28

Shameel

31-May-11 5:28

Use the T-SQL function hashbytes like this:

select hashbytes('MD5', 'your value')

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.