Re: Top 1 distinct or something like that - Database Discussion Boards

dasblinkenlight5-Jun-11 7:08

5-Jun-11 7:08

You are right about storing the date/time info in the appropriate format: the cases when it makes sense to store it differently are few and far between. The only exception to this rule that I've seen was when we needed to select across multiple dates (e.g. first ten minutes of each hour between 9 and 16). Indexing on the hour and minute components, stored in separate two-character fields, helped us avoid a full-table scan.

Re: How to build this partial SUM...

J4amieC6-Jun-11 6:19

J4amieC

6-Jun-11 6:19

Any reason you didnt make the hour and minute columns numeric, say tinyint? Hours and minutes are, after all, numeric.

Re: How to build this partial SUM...

dasblinkenlight6-Jun-11 6:38

dasblinkenlight

6-Jun-11 6:38

I'd prefer tinyints too, but it was our client's table. We were providing a solution to monitor, filter, and visualize the data for them.

Top 1 distinct or something like that

Paul E Davies2-Jun-11 23:21

Paul E Davies

2-Jun-11 23:21

Hi,

Just trying to get some test data out of one our DB - We have a table that has a Typeid (int) column and a details (nvarchar(1000)) column

What I'd like to do is get the top 1 details for each typeid (there are about 40 distinct typeid) so something along the lines of

select top 1 typeid,details from auditlog group by typeid,details,

But where the query returns the top 1 details column for each eventtypeid

like

Typid details
1 details1
2 details2
....
40 details40

Does that make sense?

Re: Top 1 distinct or something like that

Blue_Boy2-Jun-11 23:49

Blue_Boy

2-Jun-11 23:49

Use distinct

Select distinct typeid,details from auditlog order by typeid

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: Top 1 distinct or something like that

Paul E Davies3-Jun-11 0:44

Paul E Davies

3-Jun-11 0:44

Thanks for the reply.

I don't think I made it clear in my original post so apologies, but the details column could be anything (it's actually an XML fragment) so distinct will bring back all rows anyway or at least more than 1 row per eventid.

I think I'm after a sort of "top 1 distinct" combination.

Re: Top 1 distinct or something like that

Blue_Boy3-Jun-11 1:06

Blue_Boy

3-Jun-11 1:06

So, can you post data structure in your table and result which you want to get from that data?

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: Top 1 distinct or something like that

dasblinkenlight3-Jun-11 1:20

dasblinkenlight

3-Jun-11 1:20

What I'd like to do is get the top 1 details for each typeid

How do you know that a particular detail is 'top' among its peer details? Does picking a particular item matter at all? If it does not matter which row you pick as your 'top', try this:

SQL

select typeid, min(details) from auditlog group by typeid

it will decide that a detail is 'top' if its description comes first alphabetically.

If this is not what you need, you would have to provide another column to determine which detail among many possibilities for the same typeid is to be selected.

Re: Top 1 distinct or something like that

David Mujica3-Jun-11 2:43

David Mujica

3-Jun-11 2:43

I agree with the above post. You need a column like, timestamp, along with your other columns to group the TypeIds. This way you could do something like max(timestamp) and get the most recent logged event.

Good luck.

Re: Top 1 distinct or something like that

Paul E Davies3-Jun-11 3:33

Paul E Davies

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