Any suggestions other than distinct

Question

Any suggestions other than distinct

nuno.luz

Grasshopper

Points: 24
More actions
January 15, 2010 at 4:48 am

#386107

Hi,
I am making my first asp.net webpage using sql server.
I want to do a select of a table without pulling out rows where I have equal values in the IMEI field or Nome field
for instance:
Nome - IMEI - Empresa - Pontos
Vania - 23672642 - SKYt - 4
Ronaldo - 89230274 - tmn - 3
Gustavo - 82847648 - rocky - 3
Vania - 23672642 - SKYt - 2
In the last Example Vania appears twice on my Select, and I only want it to appear once.
Here is my present query:
SELECT TOP 10 [Pontos], [Nome], [Empresa] FROM [mngo] ORDER BY [Pontos] DESC
I've tried DISTINCT or GROUP BY, but I always get an error saying I cannot apply these to text fields.
Can anyone help?
Thanks
Nuno

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

It would help a lot if you could at least post the complete error message (including error message number). Even better if you could post your table definition.

I'd guess one of the columns you want to group by is text, ntext or image data type and you receive error message 306.

If so, the main question would be: Does that column have to be of text data type? Why don't you use varchar() or nvarchar()?

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 2

nuno.luz (1/15/2010)
Nome - IMEI - Empresa - Pontos
Vania - 23672642 - SKYt - 4
Ronaldo - 89230274 - tmn - 3
Gustavo - 82847648 - rocky - 3
Vania - 23672642 - SKYt - 2
Here is my present query:
SELECT TOP 10 [Pontos], [Nome], [Empresa] FROM [mngo] ORDER BY [Pontos] DESC

:pinch: Posted query would never return posted results, even columns are in different order!

Could you please post a consistent query/results set? ... including table definition?

Nevertheless something like...

select nome, imei, empresa, sum(pontos)

from mngo

group by nome, imei, empresa

...would do the trick.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

nuno.luz Grasshopper Points: 24 More actions · Answer 3

Hi,

Thank you both for your answers.

Here is some more info on the table:

Columns:

ID (PK, int, not null)

Pontos (int, null)

IMEI (nvarchar(50),null)

Nome(text,null)

Empresa(text,null)

What happens is that the way data is entering the database from a pda it repeats the person name, the IMEI, and Empresa (Company), everytime a form is completed. Only the Pontos (Points) are added/diferent.

And I want to generate a page where people can keep track of the top 10 users by points without repeating names and IMEIS.

Hope you understand now.

Best regards:

Nuno

LutzM SSC Guru Points: 107049 More actions · Answer 4

Like I said in my previous post:

You have TEXT data type for Nome(text,null) and Empresa(text,null).

It seems like there is some confusion what that data type really is supposed to be used for...

The TEXT data type can store up to 2147483647 (2 BILLION) character.

[sarcasm ON]If you ever come across a person having such a long name I'd like to see his/her passport...[sarcasm OFF]

Some trivial math:

let's assume a book will have 1000 pages with avg. 80 character per line and avg. 60 lines per page. It would take about 450 books like that to store the max amount of character...

I strongly recommend to change the data type from TEXT to VARCHAR(X), where "X" stands for an appropriate number of character. Since I don't know what you've stored in the name column, I can't tell whether 50, 100 or 200 would be enough... But definitely less than 2 billion....

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

nuno.luz Grasshopper Points: 24 More actions · Answer 5

Thank you for pointing that out 🙂

I've now changed the field types.

And I would like to do something like this:

SELECT Pontos, IMEI, Nome, Empresa

FROM tmngo

GROUP BY IMEI

ORDER BY Pontos DESC

So that the query will return the records ordered by the number of Points, but without repeating the same IMEI/person/company.

The error I am getting is the following:

Column 'tmngo.Pontos' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

What do you suggest?

Nuno

nuno.luz Grasshopper Points: 24 More actions · Answer 6

It took me awhile, but I found out how to do what I wanted:

SELECT TOP (100) PERCENT MAX(DISTINCT Pontos) AS Expr2, COUNT(DISTINCT IMEI) AS Expr1, Nome, Empresa

FROM dbo.tmngo

GROUP BY Nome, Empresa

ORDER BY Expr2 DESC

Of course I had a litle help from SSMS 🙂

Best regards:

Nuno