distinct query for table containing records more than 69347

What would be the best way to avoid distinct clause in query to get the result.My Query looks like

select distinct Col1 ,col2,col3,col4 from ABC where (1=1) group by Col1 ,col2,col3,col4 order by col1

ABC Contains records like

Col1 col2 col3 col4

a 1 1 1

a 2 1 1

B 2 2 2

B 1 1 1

;

;

;

how is the performance of disticnt query on Number of Rows ? HOw can i Improve my query performance if want to use distinct in my query? will adding indexes help to boost performance?

Thanks

You didn't tell us what is your desired outcome...

The WHERE clause can be removed.

The GROUP BY forces what you display.

Do you wish to collapse so that all 'a'/'B', etc is in one row?

If so, then something like:

Code Snippet

SELECT
Col1,
col2 = sum( Col2 ),
Col3 = sum( Col3 ),
Col4 = sum( Col4 )
FROM ABC
GROUP BY Col1

|||There is no reason you need both Distinct AND group by that I know of. How is the performance of the query as you have it? And why the where (1=1)... Is this part of a larger generated query?

DISTINCT on a single column

Hi,

I have a table of say 7 columns. I need to select all the columns but the DISTINCT clause should apply only to one column.

Example:

Name......ID

John.......1
John.......2
Mary.......3

I need only one record for John. But both Name and ID should be selected.

Thanks"I need only one record for John. But both Name and ID should be selected."

What value do you expect to be present in the ID field, given your example data?|||Any value in the ID field can be selected. It's only the name that matters.|||???

So what result to you want from:

John.......1
John.......2
Mary.......3

Option A:
John, 1
Mary, 3

Option B:
John, 2
Mary, 3

Option C:
John, 1
, 2
Mary, 3

Your logic is not clear.

blindman|||Option A:
John, 1
Mary, 3

Option B:
John, 2
Mary, 3

I can do with either Option A or Option B. what ID is selected with John is immaterial. I don't want John to be reapeated. That's all.

Thanks|||Can you provide the DDL for the table .. That might help a lot .. is the id an identity or unique column ?|||Distinct is not the correct function to use here. Distinct eliminates any duplicate rows. It acts on the entire row not just a column.

Try using Limit or TOP|||You want a group by query.

Select Name, Min(ID)
from YourTable
Group By Name

blindman|||What's "Limit"?

blindman|||Here is the acual data

Name......ID...Dept..University...

John......17...A........XYZ
John......18...B........XYZ

Now if I need only one John. Any record would do. How do I use the GROUP BY. What is this min function? My data has ID as char.

Thanks|||i think "limit" is in mysql and not in mssql ... wrong forum buddy|||Assuming ID is a unique value in your table (it better be, or your logic is not possible):

select YourTable.*
from YourTable
inner join
(select Name,
Min(ID) ID
from YourTable) DistinctIDs
on YourTable.ID = DistinctIDs.ID

This selects a single ID for each unique name and returns the data associated with that ID.

blindman|||"select Name,
Min(ID) ID
from YourTable"

Doesn't a Group By clause need to be here.|||Yeah, that would probably help... :rolleyes:

blindman

Distinct count with where clause

Hi there

I've little experience with MDX and would appreciate any help. I have a distinct count of a fact table of customer_id but I want a second measure where the distinct count has a where clause, where revenue(measure) >0. Both of the fields are in the fact table.

Thanks in advance.

Derek

Assuming you're using AS 2005, a more efficient option may be to add a named query as a 2nd fact table, based on the SQl where clause: revenue >0. Then the 2nd measure (in its own measure group) can directly be a distinct count of customer_id on the 2nd fact table.|||

Hey Deepak

Thanks for the reply. Yes I'm using 2005. Wow I wouldn't have ever thought of that so are you suggesting that I have just the player_id date and revenue in this second table, would I remove revenue from the first table? Also how will this affect performance seeing that I'm almost doubling the amount of fact rows.

Cheers,

Derek

|||

Derek,

you could create just named query, not additional table. The query should contain all necessary dimension links as the original one (or do you have just date dim?). You're right, amount of data in cube will increase, but in sake of speed. I'm using same thing for distinct invoices and I have separate named queries for different filters. Performance is really good with this approach.

Radim Hampel

|||

I supose you dont want to display the 0 value, then try this:

for example

1.- Create a count measure ME1

2.- Create your distinct count measure ME2

both of (customer_id)

3.- Create a calculated member

iif (isempty([Measures].[ME1]),([Measures].[ME1]),([Measures].[ME2]))

then when your distinctcount is 0 it is going to pick the empty value of the count measure and when is >0 it is going to pick the value of the distinct count. Then you arent going to have 0 in your results

it really works for me

Jose

|||

Thanks Guys

You are correct performance seems pretty good, and as you mentioned also Radim, I actually have more then one extra distinct count I want to do so am including field on which to perform distinct count and all foreign keys to dimension tables in each of these seperate queries. Who would of thought?

Cheers,

Derek

|||

Hey Jose

this sounds a bit like what I was expecting for an answer originally, looks interesting I'l give it a try and let you know how I got on.

Thanks

Derek

|||The above sounds good

Distinct count with where clause

Hi there

I've little experience with MDX and would appreciate any help. I have a distinct count of a fact table of customer_id but I want a second measure where the distinct count has a where clause, where revenue(measure) >0. Both of the fields are in the fact table.

Thanks in advance.

Derek

Assuming you're using AS 2005, a more efficient option may be to add a named query as a 2nd fact table, based on the SQl where clause: revenue >0. Then the 2nd measure (in its own measure group) can directly be a distinct count of customer_id on the 2nd fact table.|||

Hey Deepak

Thanks for the reply. Yes I'm using 2005. Wow I wouldn't have ever thought of that so are you suggesting that I have just the player_id date and revenue in this second table, would I remove revenue from the first table? Also how will this affect performance seeing that I'm almost doubling the amount of fact rows.

Cheers,

Derek

|||

Derek,

you could create just named query, not additional table. The query should contain all necessary dimension links as the original one (or do you have just date dim?). You're right, amount of data in cube will increase, but in sake of speed. I'm using same thing for distinct invoices and I have separate named queries for different filters. Performance is really good with this approach.

Radim Hampel

|||

I supose you dont want to display the 0 value, then try this:

for example

1.- Create a count measure ME1

2.- Create your distinct count measure ME2

both of (customer_id)

3.- Create a calculated member

iif (isempty([Measures].[ME1]),([Measures].[ME1]),([Measures].[ME2]))

then when your distinctcount is 0 it is going to pick the empty value of the count measure and when is >0 it is going to pick the value of the distinct count. Then you arent going to have 0 in your results

it really works for me

Jose

|||

Thanks Guys

You are correct performance seems pretty good, and as you mentioned also Radim, I actually have more then one extra distinct count I want to do so am including field on which to perform distinct count and all foreign keys to dimension tables in each of these seperate queries. Who would of thought?

Cheers,

Derek

|||

Hey Jose

this sounds a bit like what I was expecting for an answer originally, looks interesting I'l give it a try and let you know how I got on.

Thanks

Derek

|||The above sounds good

Disregard null parameter in WHERE clause

I have a problem optionally using a parameter to query a second key
column in an outer joined table:
DROP TABLE Sub;
DROP TABLE Main;
CREATE TABLE Main (
main_key_col INTEGER NOT NULL PRIMARY KEY,
main_data_col VARCHAR(15) NOT NULL
)'
CREATE TABLE Sub (
main_key_col INTEGER NOT NULL
REFERENCES Main (main_key_col),
sub_key_col INTEGER NOT NULL,
PRIMARY KEY (main_key_col, sub_key_col),
sub_data_col VARCHAR(15) NOT NULL
);
INSERT INTO Main VALUES (1,'Ford Model T');
INSERT INTO Main VALUES (2,'Ferrari GTB');
INSERT INTO Sub VALUES (2,1,'Red');
INSERT INTO Sub VALUES (2,2,'Yellow');
INSERT INTO Sub VALUES (2,3,'Silver');
To return the full 'denormalized' set:
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col;
Now I want to use two parameters for the respective key columns with
the sub_key_col parameter 'optional', meaning if it's NULL it is not
used in the WHERE clause. In the past I've got away with a trick like:
Sub.sub_key_col = COALESCE(@.sub_key_col, Sub.sub_key_col)
but, because of the outer join, sub_key_col can be null and NULL = NULL
removes the row, of course.
I have a solution but it isn't very satisfactory:
DECLARE @.main_key_col INTEGER, @.sub_key_col INTEGER
SET @.main_key_col = 2
SET @.sub_key_col = NULL
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col
WHERE Main.main_key_col = @.main_key_col
AND
CASE WHEN @.sub_key_col IS NULL THEN 1
WHEN Sub.sub_key_col = @.sub_key_col THEN 1
ELSE 0 END = 1
Is there a better way?
Thank you.See if this article on Dynamic Search Conditions by Erland helps:
http://www.sommarskog.se/dyn-search.html
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<decland@.petml.com> wrote in message
news:1118313681.656069.190380@.g14g2000cwa.googlegroups.com...
I have a problem optionally using a parameter to query a second key
column in an outer joined table:
DROP TABLE Sub;
DROP TABLE Main;
CREATE TABLE Main (
main_key_col INTEGER NOT NULL PRIMARY KEY,
main_data_col VARCHAR(15) NOT NULL
)'
CREATE TABLE Sub (
main_key_col INTEGER NOT NULL
REFERENCES Main (main_key_col),
sub_key_col INTEGER NOT NULL,
PRIMARY KEY (main_key_col, sub_key_col),
sub_data_col VARCHAR(15) NOT NULL
);
INSERT INTO Main VALUES (1,'Ford Model T');
INSERT INTO Main VALUES (2,'Ferrari GTB');
INSERT INTO Sub VALUES (2,1,'Red');
INSERT INTO Sub VALUES (2,2,'Yellow');
INSERT INTO Sub VALUES (2,3,'Silver');
To return the full 'denormalized' set:
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col;
Now I want to use two parameters for the respective key columns with
the sub_key_col parameter 'optional', meaning if it's NULL it is not
used in the WHERE clause. In the past I've got away with a trick like:
Sub.sub_key_col = COALESCE(@.sub_key_col, Sub.sub_key_col)
but, because of the outer join, sub_key_col can be null and NULL = NULL
removes the row, of course.
I have a solution but it isn't very satisfactory:
DECLARE @.main_key_col INTEGER, @.sub_key_col INTEGER
SET @.main_key_col = 2
SET @.sub_key_col = NULL
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col
WHERE Main.main_key_col = @.main_key_col
AND
CASE WHEN @.sub_key_col IS NULL THEN 1
WHEN Sub.sub_key_col = @.sub_key_col THEN 1
ELSE 0 END = 1
Is there a better way?
Thank you.|||DECLARE @.main_key_col INTEGER, @.sub_key_col INTEGER
SET @.main_key_col = 2
SET @.sub_key_col = NULL
SELECT Main.main_key_col, Main.main_data_col,
Sub.sub_key_col, Sub.sub_data_col
FROM Main
LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col
AND (Sub.sub_key_col = @.sub_key_col OR @.sub_key_col IS NULL)
WHERE Main.main_key_col = @.main_key_col
Jacco Schalkwijk
SQL Server MVP
<decland@.petml.com> wrote in message
news:1118313681.656069.190380@.g14g2000cwa.googlegroups.com...
>I have a problem optionally using a parameter to query a second key
> column in an outer joined table:
> DROP TABLE Sub;
> DROP TABLE Main;
> CREATE TABLE Main (
> main_key_col INTEGER NOT NULL PRIMARY KEY,
> main_data_col VARCHAR(15) NOT NULL
> )'
> CREATE TABLE Sub (
> main_key_col INTEGER NOT NULL
> REFERENCES Main (main_key_col),
> sub_key_col INTEGER NOT NULL,
> PRIMARY KEY (main_key_col, sub_key_col),
> sub_data_col VARCHAR(15) NOT NULL
> );
> INSERT INTO Main VALUES (1,'Ford Model T');
> INSERT INTO Main VALUES (2,'Ferrari GTB');
> INSERT INTO Sub VALUES (2,1,'Red');
> INSERT INTO Sub VALUES (2,2,'Yellow');
> INSERT INTO Sub VALUES (2,3,'Silver');
> To return the full 'denormalized' set:
> SELECT Main.main_key_col, Main.main_data_col,
> Sub.sub_key_col, Sub.sub_data_col
> FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col;
> Now I want to use two parameters for the respective key columns with
> the sub_key_col parameter 'optional', meaning if it's NULL it is not
> used in the WHERE clause. In the past I've got away with a trick like:
> Sub.sub_key_col = COALESCE(@.sub_key_col, Sub.sub_key_col)
> but, because of the outer join, sub_key_col can be null and NULL = NULL
> removes the row, of course.
> I have a solution but it isn't very satisfactory:
> DECLARE @.main_key_col INTEGER, @.sub_key_col INTEGER
> SET @.main_key_col = 2
> SET @.sub_key_col = NULL
> SELECT Main.main_key_col, Main.main_data_col,
> Sub.sub_key_col, Sub.sub_data_col
> FROM Main LEFT JOIN Sub ON Main.main_key_col = Sub.main_key_col
> WHERE Main.main_key_col = @.main_key_col
> AND
> CASE WHEN @.sub_key_col IS NULL THEN 1
> WHEN Sub.sub_key_col = @.sub_key_col THEN 1
> ELSE 0 END = 1
> Is there a better way?
> Thank you.
>|||COALESCE (Sub.sub_key_col, '?') = COALESCE(@.sub_key_col,
Sub.sub_key_col, '?')|||--CELKO-- wrote:
> COALESCE (Sub.sub_key_col, '?') = COALESCE(@.sub_key_col,
> Sub.sub_key_col, '?')
Now this I like because I get to use COALESCE after all! I'm now off to
order 'SQL Programming Style' <g>
Thanks everyone.

Display Results Based on Query

Hi,

I don't know if anyone has encountered this before but here goes:

I've a select clause below:
result = "Select * from person where LocalName LIKE N'" + queryLocalName + "'"

queryLocalName is an input field that allows the user to search for non-English characters in the database.

What I'm wondering is what kind of effect is the N in the where clause is having?

I can't seem to get it to work when doing it via the web. I've tested in the database itself, got it to work using the SQL Analyser but when testing on the web, it can't find because ? are appearing in the result.The 'N' in your query tells SQL Server that the bit in quotes is Unicode. That means that the data in queryLocalName has to be either ANSI for regular Latin text or Unicode for other languages. My guess is that queryLocalName is not in Unicode. From what I remember of this a year or so ago when I was involved in 'languagizing' a site it depends on the encoding of the .asp files (seriously) and the content-type meta tag in your page.

What I would do is to print out the character values of queryLocalName to verify they are Unicode -- you could enter the same text into your database via Query Analyser and print them side by side.

You SQL is correct, the data you are passing is not.|||Thanks for the explanation. I'll check that the data is in Unicode. :)